OraFAQ Forum: SQL & PL/SQL » Putting a row with a particular value on top of a sort

Home » SQL & PL/SQL » SQL & PL/SQL » Putting a row with a particular value on top of a sort (Oracle 21, Windows 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Putting a row with a particular value on top of a sort [message #685564]

Wed, 09 February 2022 14:56

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,

Report message to a moderator

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

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

ORDER BY CASE STATUS
           WHEN 'APPROVED' THEN DATE '-4712-01-01'
           ELSE STATUSDATE
         END

SY.

Report message to a moderator

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

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Or:

ORDER BY CASE STATUS
           WHEN 'APPROVED' THEN 1
           ELSE 2
         END,
         STATUSDATE

SY.

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	query
Next Topic:	Facing issie in 19c

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 26 20:49:58 CDT 2023