Select column's occurence order without group by-CodePudding

I currently have two tables, users and coupons

id	first_name
1	Roberta
2	Oliver
3	Shayna
4	Fechin

id	discount	user_id
1	20%	1
2	40%	2
3	15%	3
4	30%	1
5	10%	1
6	70%	4

What I want to do is select from the coupons table until I've selected X users.

so If I chose X = 2 the resulting table would be

id	discount	user_id
1	20%	1
2	40%	2
4	30%	1
5	10%	1

I've tried using both dense_rank and row_number but they return the count of occurrences of each user_id not it's order.

SELECT id,
       discount,
       user_id,
       dense_rank() OVER (PARTITION BY user_id)
FROM coupons

I'm guessing I need to do it in multiple subqueries (which is fine) where the first subquery would return something like

id	discount	user_id	order_of_occurence
1	20%	1	1
2	40%	2	2
3	15%	3	3
4	30%	1	1
5	10%	1	1
6	70%	4	4

which I can then use to filter by what I need.

PS: I'm using postgresql.

CodePudding user response：

You've stated that you want to parameterize the query so that you can retrieve X users. I'm reading that as all coupons for the first X distinct user_ids in coupon id column order.

It appears your attempt was close. dense_rank() is the right idea. Since you want to look over the entire table you can't use partition by. And a sorting column is also required to determine the ranking.

with data as (
    select *,
        dense_rank() over (order by id) as dr
    from coupons
)
select * from data where dr <= <X>;