Order by date range

[dlouche]

I want to get all the records from a table (no grouping) and order them first by a date range and then within that range order them by another column. For example, I have a table called Events:

ID | Date | Latitude
--------------------------------------------------------
1 | 12-01-2008 | 6
2 | 12-04-2008 | 4
3 | 12-05-2008 | 12
4 | 12-02-2008 | 4

So in this example I run the query on 12-5-2008 and would like the date range to be every two days, and the second 'order by' item to be the latitude. So the output would be:

ID | Date | Latitude
--------------------------------------------------------
2 | 12-04-2008 | 4
3 | 12-05-2008 | 12
4 | 12-02-2008 | 4
1 | 12-01-2008 | 6



ID 2 and 3 are within the last 2 days, so 2 comes before 3 (since I'm also ordering on Lat). The next date range is day 3 and 4, so ID 4 comes since it is the only item in that range, etc...

Thanks a ton for any help.

Dan

[ck9663]

Use CASE on your ORDER BY.

Try this:

set nocount on

declare @rundate smalldatetime
declare @yourtable table (id int, date smalldatetime, latitude int)


insert into @yourtable values(1 , '12-01-2008' , 6)
insert into @yourtable values(2 , '12-04-2008' , 4)
insert into @yourtable values(3 , '12-05-2008' , 12)
insert into @yourtable values(4 , '12-02-2008' , 4)

set @rundate = '12-05-2008'

select *
from @yourtable
order by 
case
	when datediff(dd,date, @rundate) <= 2 then
		0
	else 1
end asc, latitude

Happy coding!

-- CK