ORDER BY based on condition

Question

ORDER BY based on condition

gaurav-404321

SSCommitted

Points: 1593
More actions
September 29, 2010 at 10:18 pm

#241032

I have a table with columns EventID and DateofEvent
Following is the Table Creation and Insertion Script :
create table TestTable (EventID int,EventDate datetime)
insert into TestTable values(1,GETDATE()-3)
insert into TestTable values(2,GETDATE()-1)
insert into TestTable values(3,GETDATE())
insert into TestTable values(4,GETDATE()+4)
insert into TestTable values(5,GETDATE()+5)
I want a SELECT Query and A OREDER BY CLAUSE in that query which will arrange the records like suppose today is 30-Sep. Then records which are >= Todays date must be arranged in the order 1st followed by records which are less than today's date as shown in following sample output:
39/30/2010
410/4/2010
510/5/2010
19/27/2010
29/29/2010

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

You didn't specify how the time portion of the EventDate field should be dealt with so I assumed that you only wanted to look at the date not the time.

Give this a try and let me know if it works for you:

SELECT

*

FROM TestTable

ORDER BY

CASE

WHEN DATEADD(Day, 0, DATEDIFF(Day, 0, EventDate)) >= DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE())) THEN 0

ELSE 1 END,

EventDate;

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

UMG Developer (9/29/2010)
You didn't specify how the time portion of the EventDate field should be dealt with so I assumed that you only wanted to look at the date not the time.
Give this a try and let me know if it works for you:
SELECT
*
FROM TestTable
ORDER BY
CASE
WHEN DATEADD(Day, 0, DATEDIFF(Day, 0, EventDate)) >= DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE())) THEN 0
ELSE 1 END,
EventDate;

interesting logic:-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Or...

ORDER BY SIGN(DATEDIFF(day,GETDATE(),EventDate)) & 0x80000000 DESC,EventDate ASC

Far away is close at hand in the images of elsewhere.
Anon.

UMG Developer SSChampion Points: 13482 More actions · Answer 4

David, That is an interesting way to do it, but I think it is less readable for us bit-challenged people. 🙂

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :

ORDER BY

CASE

WHEN EventDate >= DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE()))

THEN CAST(0 AS tinyint) ELSE CAST(1 AS tinyint) END,

EventDate;

The CAST to tinyint insures that the sort column is only 1 byte; otherwise, SQL may use 2 (smallint) or 4 (int) instead.

Scott Pletcher, SQL Server MVP 2008-2010

UMG Developer SSChampion Points: 13482 More actions · Answer 6

scott.pletcher (9/30/2010)
Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :

Duh. I was so busy thinking about the time portion of GETDATE() the I just added the code to strip it off of EventDate too...

David Burrows SSC Guru Points: 65142 More actions · Answer 7

UMG Developer (9/30/2010)
scott.pletcher (9/30/2010)
Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :
Duh. I was so busy thinking about the time portion of GETDATE() the I just added the code to strip it off of EventDate too...

Me too :blush:

:ermm: Wonder if two selects a union and order by would be any quicker than a million+ tests (CASE)

Far away is close at hand in the images of elsewhere.
Anon.