How to get the 1st and 2nd records for each group in PostgreSQL-CodePudding

I have a table named orders in Postgres that looks as follows:

user_id      order_date       product_class
0001         2019-04-23       automotive
0001         2019-05-18       luxury goods
0001         2021-06-01       automotive
0001         2021-06-28       automotive
0001         2022-07-03       luxury goods
0001         2022-07-15       automotive
0002         2018-09-17       healthcare
0002         2018-12-03       automotive
0002         2019-03-19       healthcare
0002         2020-05-15       luxury goods
0002         2021-10-02       healthcare

Here's a SQL Fiddle: http://sqlfiddle.com/#!15/8982b/1

I would like to get a result set that returns the 1st and 2nd record for the order_date column for each product_class. The desired result set would look like this:

user_id   product_class    first_order   second_order
0001      automotive       2019-04-23    2021-06-01
0001      luxury goods     2019-05-18    2022-07-03
0002      healthcare       2018-09-17    2019-03-19
0002      automotive       2018-12-03    NULL
0002      luxury goods     2020-05-15    NULL

How would I do this? Thanks!

CodePudding user response：

Create an array and slice the part that you need:

SELECT 
    user_id
    , product_class
    , (ARRAY_AGG(order_date ORDER BY order_date))[1] AS first_order
    , (ARRAY_AGG(order_date ORDER BY order_date))[2] AS second_order
FROM
    orders
GROUP BY
  user_id, product_class
ORDER BY
  1,3,4,2;

CodePudding user response：

You can do:

with
c as (
  select *,
    row_number() over(partition by user_id, product_class 
                      order by order_date) as rn
  from orders
)
select
  c1.user_id,
  c1.product_class,
  c1.order_date as first_order,
  c2.order_date as second_order
from c c1
left join c c2 on c2.user_id = c1.user_id
              and c2.product_class = c1.product_class
              and c2.rn = 2
where c1.rn = 1
order by user_id, first_order

Result:

 user_id  product_class  first_order  second_order 
 -------- -------------- ------------ ------------ 
 0001     automotive     2019-04-23   2021-06-01   
 0001     luxury goods   2019-05-18   2022-07-03   
 0002     healthcare     2018-09-17   2019-03-19   
 0002     automotive     2018-12-03   (null)       
 0002     luxury goods   2020-05-15   (null)

See fiddle.

CodePudding user response：

My solution 
select distinct a.user_id, a.product_class, b.order_date as first_order, c.order_date as second_order
from orders a left join (
select *, row_number()over(partition by user_id,product_class order by order_date) as rn
from orders)b
on a.user_id=b.user_id and a.product_class=b.product_class and  b.rn=1
left join (select *, row_number()over(partition by user_id,product_class order by order_date) as rn
from orders)c
on a.user_id=c.user_id and c.rn=2 and a.product_class=c.product_class
order by 1,4 nulls last