Home » SQL & PL/SQL » SQL & PL/SQL » Putting a row with a particular value on top of a sort (Oracle 21, Windows 10)
Putting a row with a particular value on top of a sort [message #685564] Wed, 09 February 2022 14:56 Go to next message
orHooha
Messages: 8
Registered: April 2015
Junior Member
Hi,

I'm sorted records primarily by a date field, say STATUSDATE.

A record with a STATUS of 'APPROVED' should always be the most recent record. 'STARTED' or 'ISSUED' or 'IN PROCESS' is usually a previous record.

Problem is, sometimes that's not true. Sometimes, because of processing delays, 'APPROVED' will end up as the second record, and e.g. 'IN PROCESS' will be the most recent.

How do we do a basic sort of STATUSDATE, but with the exception that 'APPROVED' will always be the top record? Even if it's not the most recent, in terms of the STATUSDATE sort?

Assume the table name is STATUSES, and some of the fields are STATUSDATE and STATUS.


Is this relatively easy, or is it a complex query?

Thanks and regards,
Re: Putting a row with a particular value on top of a sort [message #685565 is a reply to message #685564] Wed, 09 February 2022 16:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3151
Registered: January 2010
Location: Connecticut, USA
Senior Member
ORDER BY CASE STATUS
           WHEN 'APPROVED' THEN DATE '-4712-01-01'
           ELSE STATUSDATE
         END
SY.
Re: Putting a row with a particular value on top of a sort [message #685566 is a reply to message #685565] Wed, 09 February 2022 16:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3151
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or:

ORDER BY CASE STATUS
           WHEN 'APPROVED' THEN 1
           ELSE 2
         END,
         STATUSDATE
SY.
Re: Putting a row with a particular value on top of a sort [message #685567 is a reply to message #685566] Wed, 09 February 2022 20:59 Go to previous message
orHooha
Messages: 8
Registered: April 2015
Junior Member
All set. I used the second example, thanks Solomon.

Solomon Yakobson wrote on Wed, 09 February 2022 17:08
Or:

ORDER BY CASE STATUS
           WHEN 'APPROVED' THEN 1
           ELSE 2
         END,
         STATUSDATE
SY.
Previous Topic: query
Next Topic: Facing issie in 19c
Goto Forum:
  


Current Time: Sat Jun 25 18:25:24 CDT 2022