Sort all rows apart from top row

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)

Sort all rows apart from top row

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2015-02-09 : 20:50:41

If I have a table like the one below is it possible to sort all rows in descending order by id part from the top row based on the lowest id value? The top row will always have a id value of 0 so this will always need to be left alone at the top.
So the eventual table in id order will be 0, 3, 2, 1


id  old_date     new_date     text
0   01/02/2015   05/05/2012   random comments 1
1   03/04/2013   09/09/2010   random comments 2  
2   06/06/2011   10/07/2011   random comments 3
3   01/04/2009   11/12/2009   random comments 4

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-09 : 20:57:51

You can have a dynamic ORDER BY via CASE:

select * from @t order by case when id = 0 then 99999999 else id end desc

There's probably a better way to do it than hard coding those 9s, so maybe someone else can help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-10 : 06:28:17

You can try:


select * from #testorder
order by case when id in('0') then 0 else 1 end,
id desc

or

select * from #testorder
order by case when id = 0 then 0 else 1 end,
id desc

We are the creators of our own reality!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-10 : 12:50:56

[code]
ORDER BY SIGN(id), id DESC

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources