I have a table below. There are three types (A, B, and C) available. I want to create general an SQL query. If a row has type B or type C, the row with type B or C should be listed. If a row has just type A, the row with type A should be listed.
Table;
| Number | Type |
| ------------ | ------------ |
| 1 | A |
| 1 | B |
| 2 | A |
| 3 | A |
| 3 | C |
| 4 | A |
| 5 | A |
| 6 | A |
| 6 | B |
| 6 | C |
The expected result when the query run;
Number | Type |
---|---|
1 | B |
2 | A |
3 | C |
4 | A |
5 | A |
6 | B |
6 | C |
How can I create the query?
2条答案
按热度按时间vwhgwdsa1#
I would assign each type a precedence, and only return the types of the highest precedence.
Where two types can be the same precedence (because you want to return both),
RANK()
(rather thanROW_NUMBER()
) will ensure both are assigned the same value.One option to make it a little neater could be to use
APPLY
(or a join on a lookup table) to derive the integers outside of the window function's code...Demo: https://dbfiddle.uk/2Abwpa8p
xlpyo6sf2#
Phrasing it in a different way helps finding the right command:
You want to have all rows with type 'B' and 'C', plus those with type 'A' if no other row with type 'B' or 'C' exists for that number.
So the SQL command you're looking for is: