Consider this SQL:
SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1',
'value3')
Simple enough, but is there anyway to specify that the result should be
ordered exactly like the "IN" clause states? So when this recordset
comes back, I want it like this:
my_field
------------
value2
value1
value3
Possible?
Deane
Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go
returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!
SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,
SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,
SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,
SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,
SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,
SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,
SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as Others
From
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006'
GROUP BY
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)
Output :
Aug 2006 294 0 0 80 0 0
Jan 2006 14 0 0 0 0 0
Oct 2006 336 0 0 0 0 0
Sep 2006 3262 20 24 8 16 0
How to sort the date in ascending Order ?
Jan 2006
Aug 2006
Sep 2006
Oct 2006
I am trying to do a simple SQL query..the result will be sorted alphabetically by default, but i need to specify say, if there is a "Others" it will be placed last in the list. Is there any way to write the query using ORDER BY?
e.g
Australia
China
Others
USA
and i want it to be:
Australia
CHina
USA
OThers
Bottom line question:
Does it make a difference which table is referenced first following the ON keyword in a FROM clause when creating a join?
--Example 1:
SELECT * FROM TABLE01 t1, JOIN TABLE02 t2
ON t1.ID = t2.ID
--Example 2:
SELECT * FROM TABLE01 t1, JOIN TABLE02 t2
ON t2.ID = t1.ID
Does the sequencing of the tables after the ON keyword make a difference?
Thanks in advance for your help.
Ok ok ok - I'm not very good at this, but I can't figure out how to get the 'order by' statement working correctly. It's ordering the 'ReportDate' column alphabetically and not by month order. Anyone help?
select id,
DATENAME(mm,ReportDate) As ReportDate,
max(items) as Items
from mytable
group by id, DATENAME(mm, ReportDate)
order by id, ReportDate
Output:-
id ReportDate Items
240 July 16024
240 June 14705
240 October 19392
240 September 18979
241 August 29542
241 July 27806
241 June 27500
241 October 30007
241 September 29961
As you can see, the months are coming out alphabetically and not in month order.
Any advice appreciated, especially constructive advice
I have a query that pulls records from a table based on whether is matches one or more of several criteria. I'd like to be able to order the results of this query by the number of conditions a particular row met. So a row that met 3 conditions would rank about a row that only met 2. So far I haven't been able to dent this with any attempt so I'm open to any suggestions.
View 5 Replies View RelatedI've been trying to find out why a simple query containing a couple of top-level order bys produces different results when I introduce TOP into the query. I've found nothing so far, other than the results of both queries (TOP and non-TOP) are different again if I add an index.
All the DDL and DML is below, along with the results. The database uses the BIN2 collator.
I guess there's a simple explanation for this...
DROP table employeeCREATE TABLE employee( id INTEGER IDENTITY (1, 1) NOT NULL, givenname NVARCHAR (20), familyname NVARCHAR (20), CONSTRAINT pk_employee_id PRIMARY KEY (id));insert into employee values('John', 'Smith');insert into employee values('John', 'SMITH');insert into employee values('John', 'Smyth');insert into employee values('John', 'SMYTH');goselect top 10 givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo-- Dropping top produces results in different order!select givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo-- Creating an index results in different order on both queries!CREATE INDEX idx_employee_names ON employee (familyname, givenname);goselect top 10 givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgoselect givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo
This produces the following results:
givenname familyname
-------------------- --------------------
John SMITH
John Smith
John SMYTH
John Smyth
(4 rows affected)
givenname familyname
-------------------- --------------------
John Smith
John SMITH
John Smyth
John SMYTH
(4 rows affected)
givenname familyname
-------------------- --------------------
John Smith
John SMITH
John Smyth
John SMYTH
(4 rows affected)
givenname familyname
-------------------- --------------------
John SMITH
John Smith
John Smyth
John SMYTH
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
Thanks,
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.
Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS
for example,
create table test_sort
( description varchar(75) );
insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');
then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));
select
*
from
test_sort
order by
cast( description as nvarchar(75));
Resultset1
----------
Non-A
Non-O
Noni
Nons
Resultset2
----------
Non-A
Noni
Non-O
Nons
Any ideas?
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
When on SQL Server 2000 the following statement ran without issue:
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
GROUP BY TrackID
HAVING MAX(LegNum) = 1 AND
TrackID + 'x1' IN
(
SELECT
dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
WHERE TrackID + 'x1' IN
(
SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
GROUP BY TrackID
HAVING MAX(LegNum) = 1
)
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
View 4 Replies View RelatedLets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName
From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
Thanks,
Zoop
I am looking to get a cyclic order in the order by clause. how do I do this?
for example I have 5 customers with ids like
xyz 1
xyz 2
xyz 3
xyz 4
xyz 5
when I am selecting xyz 3 I want the list to show
xyz 4
xyz 5
xyz 1
xyz 2
xyz 3
How do I do this by using the order by clause?
Hello,
can any one tell me about the difference between the following queries.
1. SELECT * FROM Symp_User ORDER BY
2. SELECT * FROM Symp_User ORDER BY ASC
I don't think there is any difference in the above queries. kinldy make me clear on this.
thnkx,
rahul jha
Can you explain the below scenario
The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
what is the reason behind this.
If I use the order by clause to sort on a date, where the date andtime stamp are the exact same for multiple records, how does SQLoutput the data?At random... or does it look at the primary key?
View 1 Replies View RelatedHello,Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8....98....0,0,0My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?Thanks, Curt.
View 4 Replies View RelatedThe following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?
SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1 ORDER BY Country_Long
Hi! I think the order by clause is driving me crazy.The following T-SQL query works: SELECT
count(*) AS c
From F_POST
Where id=@id
Order by c Ok, so far so good, but in the following case it is NOT possible to order the result set according to "count(*)": Select
T_Date AS TDATE,
count(*) AS c
From F_Post
Where id=@id
Order By case when @OrderBy = 1 then c
elseT_DATE DESC This is just strange since it is essentially the same query!? Furthermore, it seems to be inpossible to have a order-by-clause that looks like: Order By case when @OrderBy = 1 then T_Column1 ASC
elseT_Column2 DESC
Does anyone know how I can implement querys that do (almost) exactly this what the last 2 querys "should" to do?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
View 2 Replies View RelatedWhen I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results.
Any way, we can fix it.
i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
I'm using a Case statement to change an Order By clause on the fly, eg
ORDER BY case when @SortBy = 1 then s.ITEM_NAME
when @SortBy = 2 then s.ITEM_ID
when @SortBy = 3 then s.ITEM_SIZE
end
The numeric columns work fine but when @SortBy = 1, I get the following message when I try to run the sp:
Server: Msg 8114, Level 16, State 5, Procedure usp_CML_SAO_RptPresLvl, Line 95
Error converting data type varchar to numeric.
ITEM_NAME is a varchar(40) containing alphanumeric characters; ITEM_ID is a numeric(8,0) & ITEM_SIZE is a varchar(5) containing numeric characters.
Is there some rule preventing me to dynamically change the Order By if using a alphanumeric characters?
Thanks for any help you can offer
Jo
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work.
Here is the statement:
Create Procedure sp_InfoDump
(
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MinDate datetime, @MaxDate datetime
IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MinDate = Min(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MinDate = @StartDate
END
IF @EndDate = 'ALL DATES'
BEGIN
SELECT @MaxDate = Max(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MaxDate = @StartDate
END
BEGIN
SELECT tblReports.ReportName, tblReports.ReportCode,
tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime,
tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime,
tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime)
FROM tblReports
INNER JOIN
tblReportsAccess ON
tblReports.Report_ID = tblReportsAccess.Report_ID
INNER JOIN
tblAudit ON
tblReportsAccess.Audit_ID = tblAudit.Audit_ID
WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate
ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy)
END
RETURN
I have a query that returns several ordered rows where one of the fields in the ORDER BY clause is a date field (DueDate) that we use to see the most pressing deadline first. The problem is that the default value in that field (which other code translates to mean no due date) is 1/1/1900. That means that items with no due date show up before today's import deadline. I can see one potential solution where I join my results on the original table where DueDate>1/1/1900 and then back to my results so I can use an ISNULL() on the field to set a value in the future (like 1/1/9999), but that seems like a really nasty wrong round-about way to do it. I think there has to be something better.
View 4 Replies View RelatedI have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.
View 3 Replies View RelatedNeed to pass column to ORDER BY as parameter in sp (possible 8 columns out of total 30). Is there a way to do it avoiding dynamic SQL use(will be used frequently)?
View 1 Replies View RelatedHi,
I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.
The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):
QUERY 1
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=20411
order by s.ldate desc
RESULTS 1
DATE MDT IDPU Odometer DO Odometer Total Miles
12/6/2007 2041112810.6 12874.5 63.9
QUERY 2 (with Union)
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411
Union
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642
order by s.ldate desc
RESULTS 2
DATE MDT ID PU OdometerDO Odometer Total Miles
4/10/2007 20411 1207.21252.5 45.3
1/2/2007 2642 193652.6193817 164.4
As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?
Thanks!
Craig
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
View 7 Replies View RelatedHi,
Im using a select query in which im using order by clause on a column which is varchar.
Im getting wrong result on using the query,
the result output is below
1036
1373
1610
2324
255
2819
324
459
477
581
698
831
can anyone help
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.
In the query I want to set the Order By clause based on the user selection. eg.:
select * from dbo.customers where name = @CustomerName order by @SortBy
However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work
="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy
select * from dbo.customers where name = @CustomerName order by + @SortBy
I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.
Any input would be appreciated.
Thanks!
Arpan
i want to know how to use order by clause in row wise
for example, suppose the rows is
10 20 30 40
so the output is should be
10
20
30
40
Hi,
I am getting a wierd error while using order by in the over clause. Consider the following query:
select count (*) over (order by STD_CLL_CNTR_KEY) as cnt
from FCT_CLL_CS_DTLS
The error reported is :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'order'.
But at the same time this particular query seems to be working fine:
select rank () over (order by STD_CLL_CNTR_KEY) as cnt
from FCT_CLL_CS_DTLS
Am I missing something fundamental or is there a bigger issue.
Thanks in advance,
Regards,
Emil