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.
As far as I know, Redshift supports window functions: