current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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:

  1. Get me the row with the highest score
  2. The next row with the same color and the highest possible score
  3. The item with the highest score of the remaining items
  4. 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.

share|improve this question
    
What's the order logic exactly? You want the colours to be grouped based on the lowest id for the colour? –  Jakub Kania Apr 23 '13 at 11:02
    
The query in your question was the exact thing I was looking for. Thanks –  Bill Aug 10 at 5:45

2 Answers 2

up vote 2 down vote

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.

share|improve this answer
up vote 1 down vote

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

share|improve this answer
    
I edited the questions to be more precise about the order logic I need. Hope to be more clear this time. –  Mario Konschake Apr 23 '13 at 13:01
    
@MarioKonschake I updated. Though not that I look at the query above it seems it's almost the same solution. –  Jakub Kania Apr 23 '13 at 21:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.