Unanswered: SQL - Sorting a column descending order which contains two years
I am trying to sort this simplied table:
ID - Time
I want this as a result:
Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...
Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;
Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.
So, of course, it fails on all entries with a leading "-" ...
EDIT; right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...
Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it... Ok, cool, thanks!
Well... For your ranting about the name of the column, "time"... Which I hopefully said was "simplied" (typo, should've been simplified ).
Thanks a ton tonkuma, lovely and spot on.
SELECT * FROM employeeFields
WHERE employeeFields.type = 3
AND employeeID = 253150610613
CASE RIGHT(timeperiod, 1)
WHEN '-' THEN
ELSE RIGHT( REPLACE(timeperiod , '-' , '') , 4 )
Lovely, casing of the right last letter, if it is - then 9999, else replacing and then selecting the right 4 for the ordering... Quite nifty and short compared to my solution with union two seperate queries.