By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Order by date range

P: 2
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
Oct 28 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
Use CASE on your ORDER BY.

Try this:

Expand|Select|Wrap|Line Numbers
  1. set nocount on
  2.  
  3. declare @rundate smalldatetime
  4. declare @yourtable table (id int, date smalldatetime, latitude int)
  5.  
  6.  
  7. insert into @yourtable values(1 , '12-01-2008' , 6)
  8. insert into @yourtable values(2 , '12-04-2008' , 4)
  9. insert into @yourtable values(3 , '12-05-2008' , 12)
  10. insert into @yourtable values(4 , '12-02-2008' , 4)
  11.  
  12. set @rundate = '12-05-2008'
  13.  
  14. select *
  15. from @yourtable
  16. order by 
  17. case
  18.     when datediff(dd,date, @rundate) <= 2 then
  19.  
  20.     else 1
  21. end asc, latitude
Happy coding!

-- CK
Oct 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.