Postgres: select n unique rows for ID

326 views Asked by At

Using Postgres I have a scenario where I need to return a variable number of rows for a each unique id in a sql statement.

Consider I have a table of the cars a user has owned over the years.

+----+----------+---------+-------+
| ID | make     | model   | type  |
+----+----------+---------+-------+
| 1  | toyota   | camry   | sedan |  
| 1  | ford     | mustang | coupe | 
| 1  | toyota   | celica  | coupe | 
| 1  | bmw      | z4      | coupe |
| 1  | honda    | accord  | sedan |
| 2  | buick    | marque  | sedan |
| 2  | delorean | btf     | coupe |
| 2  | mini     | cooper  | coupe |
| 3  | ford     | f-150   | truck |
| 3  | ford     | mustang | coupe |
| 1  | ford     | taurus  | sedan |
+--------+----------+-------+-----+

From this table I'd only want to return two rows for each user that has a coupe and ignore the rest.

So something like. I'd also like to preserve the empty columns so the second result for ID 3 would be empty because there is only one car of type coupe. I am also working with restrictions as this has to run AWS Reshift. So, I can't use many functions. It seems this would be easy using a Top statement like in SQL server, but with Redshift restrictions and my lack of knowledge I'm not sure of the best way.

+----+----------+---------+-------+
| ID | make     | model   | type  |
+----+----------+---------+-------+
| 1  | ford     | mustang | coupe | 
| 1  | toyota   | celica  | coupe | 
| 2  | delorean | btf     | coupe |
| 2  | mini     | cooper  | coupe |
| 3  | ford     | mustang | coupe |
| 3  |          |         |       |
+--------+----------+-------+-----+

Thanks a lot for your help.

1

There are 1 answers

1
AudioBubble On BEST ANSWER

As far as I know, Redshift supports window functions:

select id, make, model, type
from (
  select id, make, model, type, 
         row_number() over (partition by id order by make) as rn
  from the_table
  where type = 'coupe'
) t
where rn <= 2
order by id, make;