Setting priority in SQL

Question

We have a table test in which we have status_cd as one of the column. Status Code can have three value - Prelim, Approved and confirmed.

I have to write a query in such a way that it should fetch the record for confirmed status cd. If confirmed status cd is not present, then fetch for Approved, if approved is not present, fetch for prelim, if prelim is also not present, fetch for null

id   rule_id   status_cd
1     1         prelim
2     1         null
3     1         approved

in above example, the query should return approved for rule_id=1

right now I am handling it in java code, i.e. I am fetching all the records for the rule id and storing that in the list and using predicates, I am filtering them out. — Sunny
– Sunny, Commented Mar 3, 2015 at 9:56
if you need the status_cd value as it is then go for another table which will have status_cd names. Here instead of that give 0-nul,1-approved-2 for prelim. then call the max with where clause for rule_id. — Iftikhar Ali Ansari
– Iftikhar Ali Ansari, Commented Mar 3, 2015 at 9:58

Raging Bull · Accepted Answer · 2015-03-03 10:04:03Z

1

Try this way:

SELECT T1.*
FROM test T1 JOIN
(SELECT *,CASE status_cd WHEN 'confirmed' THEN 1 
                         WHEN 'approved' THEN 2 
                         WHEN 'prelim' THEN 3 
                         ELSE 4 END AS Rank
 FROM test) T2 ON T1.id=T2.id AND T1.Rule_id =T2.Rule_id
ORDER BY T2.Rank
LIMIT 1

Result:

ID  RULE_ID  STATUS_CD
3   1        approved

Sample result in SQL Fiddle.

answered Mar 3, 2015 at 10:04

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

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

Comments

David McElhaney · Accepted Answer · 2015-03-03 10:09:19Z

Use the values '3 Confirmed','2 Approved', '1 Prelim' and '0 Null' or just use a default value of zero in a numerical field and set the value to 1, 2, 3 as the rule progresses (much faster execution, but avoid the null either way by using a default value of '0 Null' in the field if you keep a string implementation).

Then fetch the records with this query:

SELECT status_cd FROM YourTableName WHERE rule_id=1 ORDER BY status_cd DESC;

The first row of the query will always contain the answer you want. Additionally, the other rows will be there to confirm that all the other steps were present, etc. if you want to look at those rows too. If you know you only need the one row, then use

SELECT TOP 1 status_cd FROM ... (the rest is the same)

Collectives™ on Stack Overflow

Setting priority in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related