UNION with ORDER BY without using TOP x? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

Database Design and Application Architecture

UNION with ORDER BY without using TOP x?

Author

Topic

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-23 : 13:32:04

Hi there. I have event data that I want to show by having the date for UPCOMING events be in ASC order (dates closest to today first and the others as they come) followed by all PAST events in order of closest to today and then older. Here is sample data of how the events should be listed: (assume "today" is 8/23/2011)

Event 4 8/23/2011
Event 5 8/24/2011
Event 6 8/25/2011
Event 7 8/26/2011
Event 3 8/22/2011
Event 2 8/21/2011
Event 1 8/20/2011

I was trying to use this script, but it requires TOP operator.

SELECT * FROM ( SELECT StartDateTimeFROM MyEvents WHERE StartDateTime>= GETDATE() ORDER BY StartDateTime ASC) UpcomingEvents

UNION ALL

SELECT * FROM ( SELECT StartDateTime FROM MyEvents WHERE StartDateTime < GETDATE() ORDER BY StartDateTime DESC) PastEvents

Any help would be great! Thanks so much!

Dennis

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-23 : 15:56:28

Hi everyone. After hacking away at this for a while, a colleague and I figured out a solution. It does not look pretty, but it works! FYI. Hope this helps someone in the future.

Dennis
-----------

SELECT * FROM (
SELECT EventID, EventName, StartDateTime ,
(CASE WHEN StartDateTime >= Getdate() THEN 1 ELSE 0 END) as Orderer1 ,
(CASE WHEN StartDateTime >= Getdate() THEN EventID ELSE 0 END) as Orderer2
FROM MyEvents A WHERE A.StartDateTime >= GETDATE() and ClientID = 33

UNION --ALL

SELECT EventID, EventName, StartDateTime ,
(CASE WHEN StartDateTime < Getdate() THEN 0 ELSE 1 END) as Orderer1,
(CASE WHEN StartDateTime >= Getdate() THEN EventID ELSE 0 END) as Orderer2
FROM MyEvents A WHERE A.StartDateTime < GETDATE() and ClientID = 33) as c

order by Orderer1 DESC,
CASE WHEN StartDateTime >= Getdate() THEN StartDateTime END ASC,
CASE WHEN StartDateTime < Getdate() THEN StartDateTime END DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 02:27:42

both the statement looks similar. wont this suffice instead of union query?


SELECT EventID, EventName, StartDateTime , 
(CASE WHEN StartDateTime >= Getdate() THEN 1 ELSE 0 END) as Orderer1 ,
(CASE WHEN StartDateTime >= Getdate() THEN EventID ELSE 0 END) as Orderer2
FROM MyEvents A WHERE ClientID = 33

order by Orderer1 DESC, 
CASE WHEN StartDateTime >= Getdate() THEN StartDateTime END ASC,
CASE WHEN StartDateTime < Getdate() THEN StartDateTime END DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-24 : 08:21:03

You are correct sir! We got so focused on the UNION that when we added the CASE WHEN in the Order By, we forgot to look at removing the UNION again. Thanks! It works great! Dennis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 02:06:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources