Postgresql: Grouping with limit on group size using window functions

Question

Is there a way in Postgresql to write a query which groups rows based on a column with a limit without discarding additional rows.

Say I've got a table with three columns id, color, score with the following rows

1 red 10.0
2 red 7.0
3 red 3.0
4 blue 5.0
5 green 4.0
6 blue 2.0
7 blue 1.0

I can get a grouping based on color with window functions with the following query

SELECT * FROM (
    SELECT id, color, score, rank()
    OVER (PARTITION BY color ORDER BY score DESC)
    FROM grouping_test
) AS foo WHERE rank <= 2;

with the result

  id | color | score | rank 
 ----+-------+-------+------
   4 | blue  |   5.0 |    1
   6 | blue  |   2.0 |    2
   5 | green |   4.0 |    1
   1 | red   |  10.0 |    1
   2 | red   |   7.0 |    2

which discards item with ranks > 2. However what I need is a result like

1 red 10.0
2 red 7.0
4 blue 5.0
6 blue 2.0
5 green 4.0
3 red 3.0
7 blue 1.0

With no discarded rows.

Edit: To be more precise about the logic I need:

Get me the row with the highest score
The next row with the same color and the highest possible score
The item with the highest score of the remaining items
Same as 2., but for the row from 3.
...

Continue as long as pairs with the same color can be found, then order whats left by descending score.

The import statements for a test table can be found here. Thanks for your help.

What's the order logic exactly? You want the colours to be grouped based on the lowest id for the colour? — Jakub Kania
– Jakub Kania, Commented Apr 23, 2013 at 11:02
The query in your question was the exact thing I was looking for. Thanks — BillRobertson42
– BillRobertson42, Commented Aug 10, 2014 at 5:45

Mario Konschake · Accepted Answer · 2013-04-23 14:19:13Z

3

It can be done using two nested window functions

SELECT
  id
FROM (
  SELECT
    id,
    color,
    score,
    ((rank() OVER color_window) - 1) / 2 AS rank_window_id
  FROM grouping_test
  WINDOW color_window AS (PARTITION BY color ORDER BY score DESC)
) as foo
WINDOW rank_window AS (PARTITION BY (color, rank_window_id))
ORDER BY
  (max(score) OVER rank_window) DESC,
  color;

With 2 being the parameter of the group size.

answered Apr 23, 2013 at 14:19

Mario Konschake

2895 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jakub Kania · Accepted Answer · 2013-04-23 20:59:21Z

1

You can do ORDER BY (rank <= 2) DESC to get the rows with rank<=2 above all else:

SELECT id,color,score FROM (
SELECT id, color, score, rank()
OVER (PARTITION BY color ORDER BY score DESC),
max(score) OVER (PARTITION BY color) mx
FROM grouping_test
) AS foo 
ORDER BY 
  (rank <= 2) DESC, 
  CASE WHEN rank<=2 THEN mx ELSE NULL END DESC,
  id;

http://sqlfiddle.com/#!12/bbcfc/109

edited Apr 23, 2013 at 20:59

answered Apr 23, 2013 at 11:05

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

2 Comments

Mario Konschake Over a year ago

I edited the questions to be more precise about the order logic I need. Hope to be more clear this time.

Jakub Kania Over a year ago

@MarioKonschake I updated. Though not that I look at the query above it seems it's almost the same solution.

Collectives™ on Stack Overflow

Postgresql: Grouping with limit on group size using window functions

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related