Tricky ORDER BY clause - 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)

Tricky ORDER BY clause

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2015-01-15 : 18:24:04

Is there a way to sort the before table so it looks like the after table, so that Original_ID values are still grouped together, but not necessarily in numerical order, and the IsoDate values are sorted descending. Empty or NULL IsoDate values need no special attention and can fall where they will, as long as the Original_ID values are kept together.


Before

Original_ID	ID	IsoDate
147	        147	20/08/2009
147	        7621	20/08/2009
2221	        2221	
2221	        7620	14/01/2015
2221	        7623	14/01/2015
7500	        7500	16/10/2014
7500	        7619	16/10/2014

After

Original_ID	ID	IsoDate
147	        147	20/08/2009
147	        7621	20/08/2009
7500	        7500	16/10/2014
7500	        7619	16/10/2014
2221	        2221	
2221	        7620	14/01/2015
2221	        7623	14/01/2015

nagino
Yak Posting Veteran

75 Posts

Posted - 2015-01-15 : 19:50:33

Take it easy.
Use ORDER BY clause only like following.

SELECT
	Original_ID,
	ID,
	IsoDate
FROM YourTable
ORDER BY
	MAX(IsoDate) OVER(PARTITION BY Original_ID) ASC,
	ID ASC

-------------------------------------
From Japan
Sorry, my English ability is limited.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources