SELECT ID= - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT ID=

Author

Topic

micky--4444
Starting Member

19 Posts

Posted - 2011-06-28 : 06:47:35

Is it possible to select an id, or select id's and then order the rest.

e.g. Select * from table where id=1 or id=2 and then select the rest/or order the rest alphabetically.

Would a union be the best way to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-28 : 06:53:46

use your order clause like this:


order by
   case when id in (1,2) then 0 else 1 end,
   yourOrderByColumn1,
   yourOrderByColumn2

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-06-28 : 06:55:25

So you want IDs 1 and 2 at the top of your results and anything else after that?

If this is what you're asking then you won't need a UNION. You could order it by ID and this would give IDs 1 and 2 at the top.

If its not neccessarily 1 and 2, and could be any IDs, then you could create a SortOrder column and order by that e.g. (if you wanted 7 and 8 at the top)

SELECT Fields,CASE WHEN ID IN (7,8) THEN 1 ELSE 2 END AS SortOrder
FROM Table
ORDER BY CASE WHEN ID IN (7,8) THEN 1 ELSE 2 END,ID

Of course, You wouldn't actually need the SortOrder column in your SELECT statement but it means you can see what's going on whilst its in the development phase.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
http://www.hollowtreewebdesign.co.uk - a web design company in its infancy

micky--4444
Starting Member

19 Posts

Posted - 2011-07-06 : 07:11:19

thanks guys.

In theboyholty's example, what if wanted 8 to come before 7?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 07:29:00

case
when id=8 then 1
when id=7 then 2
else 3
end

No, you're never too old to Yak'n'Roll if you're too young to die.

micky--4444
Starting Member

19 Posts

Posted - 2011-07-22 : 05:00:26

Hi guys,

although this worked for me:

SELECT [field1], [field2], [field3] FROM [Table1] order by case when [field1] in ('text1','text2') then 0 else 1 end,[field2]

I'm not sure what the 'then 0 else 1 end' does? It'd be great to understand the theory.

thanks

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-07-22 : 07:55:53

The CASE statement creates a field - as its not in your SELECT list, you don't see it. But you can add it in order to see what's going on - otherwise its just used for ordering the data.

If the value of field1 is either 'text1 or text2, it returns a zero and if its anything else it returns a 1

Therfore when your data is ordered by this field, the zeroes will be placed before the 1s hence the field1 values being text1 or text2 will come at the top of the results set.

To see what's going on add "case when [field1] in ('text1','text2') then 0 else 1 end as OrderBy" to your select list.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources