Order by Dates - Blanks coming first

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by Dates - Blanks coming first

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-19 : 06:05:51

Hey All,

I 2 columns Date Added and Date Updated

I am trying to sort my table by date updated.

Not all records will have date updated date, when I am doing the sort the problem is the records with a blank are coming in first then my date updated ones sorted come through.

How can I get the sort to work with most upto date dateupdated records first then the records with no date updated which are then sorted as normal by dateAdded.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-19 : 06:27:37

Blank, or NULL?

SELECT *
FROM Table1
ORDER BY COALESCE(DateUpdated, DateAdded) DESC

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-19 : 06:28:40

SELECT * FROM tableName
order by dateColumn desc

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-19 : 06:34:06

Would be blank this query

SELECT TBLREQUESTS.REF_NO, TBLREQUESTTYPES.TYPE_NAME, TBLREQUESTS.DATEADDED, TBLREQUESTS.STATUS_ID, TBLREQUESTS.SUPERVISOR_ID , TBLREQUESTSTATUS.STATUS_NAME, TBLLOCATIONS.FACILITY_NAME, TBLREQUESTS.ASSIGN_TO, TBLLOCATIONS.FLOOR, TBLLOCATIONS.ROOM, TBLREQUESTS.ID , (SELECT MAX(TBLJOBHISTORY.DATEADDED) FROM TBLJOBHISTORY WHERE TBLJOBHISTORY.REQUEST_ID = TBLREQUESTS.ID) AS LAST_DATE FROM TBLREQUESTS INNER JOIN TBLLOCATIONS ON TBLREQUESTS.LOCATION_ID = TBLLOCATIONS.ID INNER JOIN TBLREQUESTSTATUS ON TBLREQUESTSTATUS.ID = TBLREQUESTS.STATUS_ID INNER JOIN TBLREQUESTTYPES ON TBLREQUESTS.REQUEST_TYPE_ID = TBLREQUESTTYPES.ID WHERE TBLREQUESTS.STATUS_ID = 4

where last date is dateUpdated

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-19 : 06:35:59

Tried
COALESCE(DateUpdated, DateAdded) DESC

Blanks still came first

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-19 : 06:40:11

try this
order by case when datecolumn <> '' then 0 else 1 end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-19 : 06:53:55

What datatype are you using for the two columns?
If they are NOT datetime, you will always having trouble with sorting.