CODEWITHSUNDEEP
sqloracle-database
Leeish
Leeish

Reputation: 5213

SQL Change Rank based on any value in group of values

I'm not looking for the answer as much as what to search for as I think this is possible. I have a query where the result can be as such:

| ID | CODE | RANK |

I want to base rank off of the code so my I get these results

| 1 | A | 1 |
| 1 | B | 1 |
| 2 | A | 1 |
| 2 | C | 1 |
| 3 | B | 2 |
| 3 | C | 2 |
| 4 | C | 3 |

Basically, based on the group of IDs, if any of the CODEs = a certain value I want to adjust the rank so then I can order by rank first and then other columns. Never sure how to phrase things in SQL.

I tried CASE WHEN CODE = 'A' THEN 1 WHEN CODE = 'B' THEN 2 ELSE 3 END rank ORDER BY rank DESC

But I want to keep the ids together, I don't want them broken apart, I was thinking of doing all ranks the same based on the highest if I can't solve it another way?

Thoughts of a SQL function to look at?

Upvotes: 0

Views: 963

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You could use the MIN() OVER() analytic function to get the minimum rank value per group, and just order by that;

WITH cte AS (
  SELECT id, code, 
    MIN(CASE WHEN code='A' THEN 1 WHEN code='B' THEN 2 ELSE 3 END)
    OVER (PARTITION BY id) rank
FROM mytable 
)
SELECT * FROM cte
ORDER BY rank, id, code

An SQLfiddle to test with.

Upvotes: 1

Related Questions