I'm not an expert of ANSI SQL and I need to write the following query.
This is the table from which I start:
| ID | Max_Recurrency | Priority |
|---|---|---|
| abc | 2 | 1 |
| abc | 2 | 450 |
| abc | 2 | 12 |
| def | 1 | 827 |
| def | 1 | 44 |
| def | 1 | 112 |
| ghi | 2 | 544 |
| ghi | 2 | 4 |
| ghi | 2 | 95 |
| ghi | 2 | 25 |
The output I need is something like this:
| ID | Max_Recurrency | Priority |
|---|---|---|
| abc | 2 | 450 |
| abc | 2 | 12 |
| def | 1 | 827 |
| ghi | 2 | 544 |
| ghi | 2 | 95 |
In other words, I need to select the ID of the record as many times as is indicated in the Max_Recurrency field and select the records with the highest Priority, i.e. excluding those with the lowest Priority if the Max_Recurrency field has a value less than the number of times the ID is repeated in the table.
Can anyone help me?
CodePudding user response：
We can use ROW_NUMBER here:
WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Priority DESC) rn
FROM yourTable t
)
SELECT ID, Max_Recurrency, Priority
FROM cte
WHERE rn <= Max_Recurrency;