Select first and nth order for each client

Question

I'm trying to display first and fifth order_id for each user.

Table looks like this:

+----------+-----------+-------------------+
| Order_ID | Client_ID | Datetime          |
+----------+-----------+-------------------+
| 1        | 1         | YYYYMMDD HH:MM:SS |
+----------+-----------+-------------------+
| 2        | 1         | YYYYMMDD HH:MM:SS |
+----------+-----------+-------------------+
| 3        | 2         | YYYYMMDD HH:MM:SS |
+----------+-----------+-------------------+

I wrote something like this:

select 
  t.client_id, 
  t.order_id as first_order, 
  t2.order_id as fifth_order,
  t.datetime as first_dt,
  t2.datetime as fifth_dt,
from
(
  select o.client_id, o.order_id, o.datetime,
  row_number() over(partition by o.client_id order by o.datetime) as rn
  from "OhMyTable" as o
) as t
  left join
    (
  select o.client_id, o.order_id, o.datetime,
  row_number() over(partition by o.client_id order by o.order_id) as rn
  from "OhMyTable" as o
      order by o.order_id
  ) as t2
  on t.client_id = t2.client_id
where t.rn = 1 and t2.rn = 5

But I want to see clients who made their first orders but don't have fifths. There should be NULL, but I can't understand how to make it. :c

Answer 1

Put the where t2.rn = 5 condition into the left join:

select 
  t.client_id, 
  t.order_id as first_order, 
  t2.order_id as fifth_order,
  t.datetime as first_dt,
  t2.datetime as fifth_dt,
from
(
  select o.client_id, o.order_id, o.datetime,
  row_number() over(partition by o.client_id order by o.datetime) as rn
  from "OhMyTable" as o
) as t
  left join
    (
  select o.client_id, o.order_id, o.datetime,
  row_number() over(partition by o.client_id order by o.order_id) as rn
  from "OhMyTable" as o
      order by o.order_id
  ) as t2
  on t.client_id = t2.client_id
  and t2.rn = 5
where t.rn = 1 and t2.datetime is null