postgresql: Join n-th row

Question

I have a table users and a table orders.

The table users has a primary key user_id and some other fields. The table orders has a foreign key user_id, a name order_name and a date order_date.

For a legacy export, I need to build a query that returns the data in the form user_id | order_name1 | order_name2 | order_name3 where order 1-3 are the three most recent orders of the customers.

My idea is to query on customers and make three joins with select to orders

SELECT 
 users.*,
 order1.order_name AS order_name1,
 order2.order_name AS order_name2,
 order3.order_name AS order_name3
 FROM users AS users
JOIN (
 SELECT
  user_id,
  order_name,
  order_date
 FROM orders ORDER BY order_date DESC OFFSET 0 LIMIT 1)
AS order1 ON order1.user_id=users.user_id
JOIN (
 SELECT
  user_id,
  order_name,
  order_date
 FROM orders ORDER BY order_date DESC OFFSET 1 LIMIT 1)
AS order2 ON order2.user_id=users.user_id
JOIN (
 SELECT
  user_id,
  order_name,
  order_date
 FROM orders ORDER BY order_date DESC OFFSET 2 LIMIT 1)
AS order3 ON order3.user_id=users.user_id

However, this does not work as it only returns one row each from orders, and not specifically for each user.

How to write such a query?

score 2 · Accepted Answer · 2016-02-11 10:35:22Z

2

You can use a window function to number the rows for each user:

SELECT users.*,
       o.order_name,
       o.rn as order_number
FROM users AS users
JOIN ( SELECT user_id,
              order_name,
              order_date, 
              row_number() over (Partition by user_id) order by order_date desc as rn
       FROM orders
) AS o ON o.user_id = users.user_id and o.rn <= 3;

However the above will not give you three columns, but three rows for each user. To convert that into columns, you need to apply the poor man's pivot pattern by applying a group by:

SELECT users.user_id,
       max(case when o.rn = 1 then o.order_name end) as order_name1,
       max(case when o.rn = 2 then o.order_name end) as order_name2,
       max(case when o.rn = 3 then o.order_name end) as order_name3
FROM users AS users
JOIN ( SELECT user_id,
              order_name,
              order_date, 
              row_number() over (Partition by user_id) order by order_date desc as rn
       FROM orders
) AS o ON o.user_id = users.user_id and o.rn <= 3;
GROUP BY users.user_id;

If users.user_id is the primary key of that table, you can add additional columns from the users table without the need to add them to the group by.

Since Postgres 9.4, the expression

max(case when o.rn = 1 then o.order_name end) as order_name1

can also be written as:

max(o.order_name) filter (where o.rn = 1) as order_name1

edited Feb 11, 2016 at 10:35

answered Feb 11, 2016 at 10:20

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

user3207838 Over a year ago

You saved my life! Thanks for pointing out the window function!

Collectives™ on Stack Overflow

postgresql: Join n-th row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related