sort by date desc - but i want nulls on top 1

[kev510]

hello everyone,

I am trying to create a query that will sort a DATE column in descending order, but I need nulls to show up on top (for display purposes). Is this possible, and how can it be done?

Please let me know if more information is needed.
Thanks in advance!!

 

[r937]

ORDER BY
CASE WHEN mydate IS NULL THEN 0 ELSE 1 END, mydate DESC

r937.com | rudy.ca

 

[kev510]

would this still sort the dates in a descending order?

 

[r937]

sure -- try it!!

r937.com | rudy.ca

 

[kev510]

sorry I should've tested it out before asking.
works like a charm!!
Thank you so much.

but now I have to figure out how I can use it in my stored procedure... apparently it's not cooperating with the UNION statement (I get an error message saying "ERROR: 104 ORDER by items must appear in the select list if the statement contains the UNION operator")

 

[r937]

please show your entire query

r937.com | rudy.ca

 

[kev510]

here's the query -

SELECT * FROM OPENROWSET('sqloledb', 'server=00111;database=unet;uid=abc;pwd=abc',
'select ern, ertitle, duration, claimcount, QueryType, EROtherExclusions, testmode, dateadd(hh,1,Executed) as DateSent, runorder from TABLE')
union

SELECT * FROM OPENROWSET('sqloledb', 'server=00110;database=unet;uid=abc;pwd=abc',
'select ern, ertitle, duration, claimcount, QueryType, EROtherExclusions, testmode, dateadd(hh,1,Executed) as DateSent, Executed, runorder from TABLE') order by
case when executed is null then 0 else 1 end, datesent desc, ern desc

 

[AlexCuse]

You don't have a column called executed in your select list. Maybe you need this?

order by case when datesent is null then 0 else 1 end, datesent desc, ern desc

Hope this helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[kev510]

oops!
I gave you the wrong query...
the one I posted was the one I was working on (sorry for the trouble)

this is the query that is giving me problems -

SELECT * FROM OPENROWSET('sqloledb', 'server=SERVER1;database=unet;uid=abc;pwd=abc',
'select ern, dateadd(hh,1,Executed) as DateSent from TABLE')
union

SELECT * FROM OPENROWSET('sqloledb', 'server=SERVER0;database=unet;uid=abc;pwd=abc',
'select ern, dateadd(hh,1,Executed) as DateSent from TABLE') order by
case when datesent is null then 0 else 1 end, datesent desc, ern desc

 

[r937]

UNION queries require that each of the SELECTs returns the same number of columns



r937.com | rudy.ca

 

[kev510]

thank you for your help!
I got this figured out.

Have a great day!

 

[JarlH]

ANSI SQL 2003 also has a NULLS FIRST/LAST option.

E.g. ORDER BY datecol NULLS FIRST

(NULLS FIRST is non-core feature, included in T611, "Elementary OLAP operations")