Select a record a variable number of times based on two different fields on the same table

Question

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?

Answer 1

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;