6

I have the following query:

SELECT s.id,
       s.name,
       s.prom,
       s.some_other_field,
       ... many more fields also
FROM mytable s
INNER JOIN (on some other tables etc.)

In the case where there are more than 6 records with the same name (s.name), I want to group these items together as one row (I don't care which other data is grouped into that row, i.e. the first record of that group would be fine).

Is this possible in Postgres? I guess it is a conditional group by in a sense that I want to group the records where the same name appears more than 6 times, whilst returning all the other records as normal.

Any help is much appreciated - thanks!

harman_kardon
  • 1,567
  • 5
  • 32
  • 49
  • s.id will be the same or this may vary? – Naved Jan 11 '12 at 09:13
  • s.id would be different but for the grouped row should ideally contain the data from the first row of that group (if that makes sense!) – harman_kardon Jan 11 '12 at 09:16
  • SELECT [field names] FROM [table name] WHERE [conditions] (INNER/OUTER JOINS) .... GROUP BY [field name] HAVING [conditions] – Acn Jan 11 '12 at 09:19

1 Answers1

9

you can do this:

select * from (

    SELECT s.id,
           s.name,
           s.prom,
           s.some_other_field,
           ... many more fields also,
           row_number() over (partition by s.name order by s.id) as rnk,
           count(*) over (partition by s.name) cnt
    FROM mytable s
    INNER JOIN (on some other tables etc.)

) a
where cnt < 6 or (cnt >=6 and rnk = 1)
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76