Order by statment in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Order by statment in sql

Author

Topic

agupta2009
Starting Member

1 Post

Posted - 2009-01-08 : 11:35:46

Hello Everybody,

Here is the query:

SELECT SEARCH_ATTR_ID, ATTRIBUTE_TEXT FROM SEARCH_ATTRIBUTE
WHERE SEARCH_ATTRIBUTE_ID IN (958,2190,946)
ORDER BY SEARCH_ATTRIBUTE_ID

Accepted Result: The output should be display as per the search_attribute_id order

SEARCH_ATTRIBUTE_ID ATTRIBUTE_TEXT
958 Last_Name
2190 Middle Name
946 First_Name

Current Result Displays

SEARCH_ATTRIBUTE_ID ATTRIBUTE_TEXT
946 First_Name
958 Last_Name
2190 Middle Name

Please Help

Thanks,
Ash

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:42:08

ORDER BY SEARCH_ATTRIBUTE_ID does not mean order in which values are given in IN condition. it takes the values of SEARCH_ATTRIBUTE_ID and returns result in increasing order of numeric value contained which is why you get 946 ,958, 2190 as 946 <958< 2190..

To get what you expect, you need this


SELECT SEARCH_ATTR_ID, 
ATTRIBUTE_TEXT,
CASE SEARCH_ATTR_ID
WHEN 958 THEN 1
WHEN 2190 THEN 2
WHEN 946 THEN 3
END AS OrdVal
FROM SEARCH_ATTRIBUTE 
WHERE SEARCH_ATTRIBUTE_ID IN (958,2190,946) 
ORDER BY OrdVal

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 05:19:31

u can write that case statement in order by statement also
SELECT SEARCH_ATTR_ID,
ATTRIBUTE_TEXT
FROM SEARCH_ATTRIBUTE
WHERE SEARCH_ATTRIBUTE_ID IN (958,2190,946)
ORDER BY CASE SEARCH_ATTR_ID
WHEN 958 THEN 1
WHEN 2190 THEN 2
WHEN 946 THEN 3
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 12:30:10

quote:
Originally posted by bklr

u can write that case statement in order by statement also
SELECT SEARCH_ATTR_ID,
ATTRIBUTE_TEXT
FROM SEARCH_ATTRIBUTE
WHERE SEARCH_ATTRIBUTE_ID IN (958,2190,946)
ORDER BY CASE SEARCH_ATTR_ID
WHEN 958 THEN 1
WHEN 2190 THEN 2
WHEN 946 THEN 3
END

why repeat entire case expression in order by when you can simply specify the alias?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources