how to take a single value in column depends on two columns-CodePudding

I'm using SQL Server and I have table like this:

id	size	reign
1	large	Riyadh
1	small	Riyadh
2	large	Makkah
2	medium	Makkah
2	small	Jeddah
3	medium	Dammam

I want a query to take only one size for each regain and id. For example in id "1", I want to remove the second value ("small") Notice: I can't alter or make changes in the table.

The result should be like this:

id	size	reign
1	large	Riyadh
2	large	Makkah
2	small	Jeddah
3	medium	Dammam

CodePudding user response：

Assuming you want to prioritize large, medium, and small, in this order, we can try using ROW_NUMBER as follows:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id, reign
                                 ORDER BY CASE size WHEN 'large'  THEN 1
                                                    WHEN 'medium' THEN 2
                                                    WHEN 'small'  THEN 3 END) rn
    FROM yourTable
)

SELECT id, size, reign
FROM cte
WHERE rn = 1
ORDER BY id;

CodePudding user response：

If your data is always in this simple form (meaning there are those three sizes only) and if you always want to select large first if present, else medium first if present and last small, this can just be done using MIN and GROUP BY with a good ORDER BY clause:

SELECT id, MIN(size) AS size, reign
FROM yourtable
GROUP BY id, reign
ORDER BY id, size;

This query will produce exactly the result shown in your question.

Verify this here: db<>fiddle

If this logic is not sufficient to meet your requirements, please edit your question and explain more detailed what you need.