Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order By Date

Posted on 2011-09-27
9
Medium Priority
?
224 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:sanjshah12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36713062
Depends on what you mean by "closest to today".  For example, this orders the list by DATEDIFF:

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

Open in new window


An example of what you had in mind would be helpful.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36713113
I think it's even simpler.  Just order in descending order.

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

Greg

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36713135
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sanjshah12
ID: 36713183
matthewspatrick,

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

Expert Comment

by:Patrick Matthews
ID: 36713212
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
0
 

Author Comment

by:sanjshah12
ID: 36713294
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

0
 
LVL 3

Expert Comment

by:Srm74
ID: 36714629


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





0
 

Author Comment

by:sanjshah12
ID: 36716027
Yes SRM Patrick's answer is correct.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36716277
Patrick,

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

Greg

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question