Fetch single records based on priority via oracle in single query

Question

I have a table in Oracle SQL as follows.

  Account_Id     Account_No              BS               STATUS
    1               10                    V                 ACTV
    2               11                    S                 DIS
    3               12                    E                NOTACTV
    4               10                    S                 DIS
    5               12                    E                 ACTV
    6               12                    V                 ACTV

I need to get the Account Number (one record) with below Mentioned priority.

 1) BS = V && STATUS = ACTV
 2) BS = S && STATUS = ACTV
 3) BS = E && STATUS = ACTV
 4) BS = V && STATUS = NOTACTV
 5) BS = S && STATUS = NOTACTV
 6) BS = E && STATUS = NOTACTV
 7) BS = V && STATUS = DIS
 8) BS = S && STATUS = DIS
 9) BS = E && STATUS = DIS

I tried using three sql queries with V and checking status ACTV and NOTACTV. I wanted to fetch the data in one sql query so as to optimize this.

How to do this??

Gordon Linoff · Accepted Answer · 2018-06-01 12:13:06Z

2

I think you want:

select t.*
from (select t.*
      from t
      order by (case status when 'ACTV' then 1 when 'NOTACTIV' then 2 WHEN 'DIS' then 3 else 4 end),
               (case BS when 'V' then 1 when 'S' then 2 when 'E' then 3 else 4 end)
    ) t
where rownum = 1;

If you are using Oracle 12c+, you can use fetch first and you don't need the subquery.

Here is a working version.

edited Jun 1, 2018 at 12:13

answered May 31, 2018 at 12:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manan Kapoor Over a year ago

while running it is showing error: SQL command not properly ended. also, if I have to check for particular/multiple account number , adding account number is where clause will work fine or any other change needed.

Gordon Linoff Over a year ago

@MananKapoor . . . There is nothing obviously wrong with this version of the query.

Collectives™ on Stack Overflow

Fetch single records based on priority via oracle in single query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related