max function with more than one result for every group

user_14668421 0
Viewed 29

I'm looking for help with postgresql query.

Example input data are as follows:

pk  name
=========================
1   | notebook cz-2001
2   | notebook cz-2002
3   | notebook cz-2003
4   | notebook cz-2003
5   | notebook cz-2003
6   | notebook cz-2004
7   | notebook cz-2004
8   | notebook cz-2004
9   | notebook cz-2004
10  | notebook cz-2005
11  | notebook cz-2006
12  | notebook cz-2007
13  | notebook cz-2008
14  | notebook cz-2009

Some rows have same value in name column (notebook cz-2003, notebook cz-2004)

I can get one maximum pk value for grouped names by this query:

select * from test_group where pk in
(
select max(pk) from test_group group by name order by name
)

Result:

pk  name
=========================
1   | notebook cz-2001
2   | notebook cz-2002
5   | notebook cz-2003
9   | notebook cz-2004
10  | notebook cz-2005
11  | notebook cz-2006
12  | notebook cz-2007
13  | notebook cz-2008
14  | notebook cz-2009

but I have no idea and that is my question how to get for example two (three, four...) highest pk values for grouped names.

Expected result for example for two highest pk:

pk  name
=========================
1   | notebook cz-2001
2   | notebook cz-2002
4   | notebook cz-2003
5   | notebook cz-2003
8   | notebook cz-2004
9   | notebook cz-2004
10  | notebook cz-2005
11  | notebook cz-2006
12  | notebook cz-2007
13  | notebook cz-2008
14  | notebook cz-2009

Could you give me some hints how to achieve that?

Thanks

JP

postgresql sql
2 Answers

You could use the row_number() window function for that:

demo:db<>fiddle

SELECT pk, name
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY pk DESC)
    FROM my_table
) s
WHERE row_number <= 2    -- or any other value

row_number() window function add a row number to a certain ordered group. Here your group is the name column and the order is by pk (descending because you want the highest numbers counted first).

With the WHERE clause you can filter the first n records (which were numbered with 1, 2, ...) or any other elements you like.

user_39842210

One method is window functions:

select tg.*
from (select tg.*,
             row_number() over (partition by name order by pk desc) as seqnum
      from test_group tg
     ) tg
where seqnum <= ?;

Another method -- more in line with your current approach -- is a correlated subquery:

select tg.*
from test_group tg
where tg.pk >= any (select tg2.pk
                    from test_group tg2
                    where tg2.name = tg.name
                   );
user_11440350
Related