Link to home
Start Free TrialLog in
Avatar of sanjshah12
sanjshah12Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Order By Date

Hi,

I've been asked a strange request to see if I can use the Order By Date in a query but display the list closet to today first - is this possible?

      SELECT id, company, requestor
      From tbl_oders
      Order By StartDate

Thanks

ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think it's even simpler.  Just order in descending order.

SELECT id, company, requestor
From tbl_oders
Order By StartDate DESC

Greg

Greg,

Maybe :)

Depends on just what "closest to today" means, and whether all of the dates are in the future, all in the past, or a mix.  For example, theoretically it could be this:

SELECT id, company, requestor
From tbl_oders
Order By ABS(DATEDIFF(day, StartDate, GETDATE()))

Open in new window


That orders everything by "distance from today", without caring about the "direction" of the difference...

Patrick
Avatar of sanjshah12

ASKER

matthewspatrick,

yes I think yours solution is what I am looking for, what is the difference between the ABS and DateDiff query?
sanjshah12,

First things first.  

1) Please define exactly how you want the sorting to be done.  What are the rules?

2) Is StartDate always in the future, always in the past, or a mix?

3) Please provide a few sample rows of data, and indicate how those sample data should be sorted

Patrick
Patirck,

1. the listing (sorting) should be form displayed from how many days it is away from todays date
2. the StartDate contains a mixture of dates
3. below is some sample data

Hop this makes sense.
1	user1	31/05/2011 00:00
2	user2	31/05/2011 00:00
3	user3	14/06/2011 00:00
4	user4	14/06/2011 00:00
5	user5	20/07/2011 00:00
6	user6	13/07/2011 00:00
7	user7	14/07/2011 00:00
8	user8	13/07/2011 00:00
9	user9	19/07/2011 00:00
10	user10	04/09/2011 00:00
11	user11	04/09/2011 00:00
12	user12	04/09/2011 00:00
13	user13	04/09/2011 00:00
14	user14	12/09/2011 00:00
15	user15	13/09/2011 00:00
16	user16	13/09/2011 00:00
17	user17	13/09/2011 00:00
18	user18	13/09/2011 00:00
19	user19	13/09/2011 00:00
20	user20	11/09/2011 00:00
21	user21	27/09/2011 00:00
22	user22	27/09/2011 00:00
23	user23	30/09/2011 00:00
24	user24	29/09/2011 00:00
25	user25	29/09/2011 00:00

Open in new window

Avatar of Srm74
Srm74



As matthewspatrick,wrote,

SELECT id, company, requestor
From tbl_oders
Order By DATEDIFF(day, StartDate, GETDATE())

Open in new window


I believe that what you are looking for..

The ABS stands for Absolute, which means it removes the sign from the calculation..
I.e. if you had an order tomorrow, it would give the value -1 and so on..
Applying ABS turns this -1 into 1, giving tomorrow the same position as yesterday.

But i dont believe you have any orders for tomorrow, so you dont need this.

Hope it helps





Yes SRM Patrick's answer is correct.
Patrick,

Good point about the direction.  I was assuming only past dates, which is probably a bad assumption.  Nice solution!

Greg