CODEWITHSUNDEEP
sql
Tomas Breuer
Tomas Breuer

Reputation: 27

ORDER BY With possible missing column

I have this DB with 2 fields, updateDate, and createDate, pretty self explanatory.

Now, my issue is that updateDate might not exist at times, and just a createDate. I want to order by newest date, but I can't seem to find a way to order correctly, my first approach was to do the following:

ORDER BY
CASE 
WHEN "updateDate" = '' THEN "createDate"
ELSE "updateDate"
END
DESC

And this partially works, it gives priority to updateDate, and then sorts by createDate. However, the issue occurs that it gives priority to sorting by updateDate, and then by createDate (Not entirely wrong, updateDate should have priority over createDate on the same date), so if there was a createDate with no updateDate newer than the updateDates, it will be placed at the bottom anyway like this:

Item | updateDate | createDate
1       24/1/2019     1/1/2005
2       23/1/2019     1/1/2005
3       22/1/2019     1/1/2005
4                    23/1/2019
5                    22/1/2019

How should I do this so it sorts like this?

Item | updateDate | createDate
1       24/1/2019     1/1/2005
2       23/1/2019     1/1/2005
4                    23/1/2019
3       22/1/2019     1/1/2005
5                    22/1/2019

Thanks

EDIT: The SQL engine is SAP HANA, which is kind of undocumented, but from my testing, regular SQL like MySQL should work.

Upvotes: 1

Views: 140

Answers (4)

Jim Macaulay
Jim Macaulay

Reputation: 5165

SELECT * FROM TABLE ORDER BY nvl(updateDate, createDate);

You can use this as well

Upvotes: 0

Ivan Yurov
Ivan Yurov

Reputation: 1618

If you want to fall back to createDate when updateDate is missing, coalesce() might work:

SELECT * FROM TABLE ORDER BY coalesce("updateDate", "createDate");

Upvotes: 4

Eduardo Spagna
Eduardo Spagna

Reputation: 39

Try use

SELECT FIRST_VALUE(updateDate) 
    OVER (PARTITION BY Item ORDER BY updateDate DESC) as [last_update]

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133390

You could use an IFNULL for order by 

select * 
from my_table  
order by  ifnull(updateDate, createDate) DESC

http://sap.optimieren.de/hana/hana/html/sql_function_ifnull.html

Upvotes: 1

Related Questions