CODEWITHSUNDEEP
sqlsql-order-by
Zaki
Zaki

Reputation: 5600

Order by date issue

I have a table with two columns :

datecreated 
datemodified

I have written the following query to get records in past 7 days from today :

Select * from mytable
Where  ((datecreated < GETDATE() - 7) or 
(datemodified <> null or datemodified < GETDATE() - 7))
Order By datemodified, datecreated asc

Is there any way to order by different columns e.g. if we have two datemodified :

1. 29/02/2012
2. 01/03/2012

and datecreated is :

1. 27/02/2012 
2. 28/02/2012

the select would return datemodified old to recent date and date created old to recent:

1. 29/02/2012
2. 01/03/2012
1. 27/02/2012 
2. 28/02/2012

Upvotes: 0

Views: 184

Answers (2)

patmortech
patmortech

Reputation: 10219

The following should do what you are looking for -- it first orders by the datemodified, if there is one, and sticks all the ones without a datemodified at the end and orders them by the datecreated.

SELECT *
FROM MyTable
WHERE (datecreated < getdate() - 7)
   OR (datemodified IS NOT NULL AND datemodified < getdate() - 7)
ORDER BY coalesce(datemodified, '1/1/9999', datecreated)

Edit

I think I misread the result set you want. If you want only one date returned, that is either datemodified or datecreated, then you change the select to be the following:

SELECT coalesce(datemodified, datecreated)
FROM ....

Upvotes: 1

JamieSee
JamieSee

Reputation: 13030

Don't use <> with NULL, not all SQL versions support that, use IS NULL instead. There are a few different ways to accomplish this. In your case you probably want a UNION or a UNION ALL if you want to preserve duplicate values.

SELECT     datecreated, datemodified
FROM         (SELECT     TOP (100) PERCENT datecreated, datemodified
                       FROM          mytable
                       WHERE      (datecreated > GETDATE() - 7)
                       ORDER BY datecreated)
UNION ALL
SELECT     datecreated, datemodified
FROM         (SELECT     TOP (100) PERCENT datecreated, datemodified
                       FROM          mytable
                       WHERE      (datemodified IS NULL) OR
                                              (datemodified > GETDATE() - 7)
                       ORDER BY datemodified)

Upvotes: 1

Related Questions