MariaDB: Select val_1, max(value_2), group by value_3

Question

I have the following columns:

| order_id | client_id | order_timestamp | buyer_id | (all INTs)

It started with the easy-sounding task "Show me the buyer of the last order for each client", so basically

SELECT
    client_id,
    max(order_timestamp),
    buyer_id
FROM table t
GROUP BY client_id;

if GROUP BY would work as one would expect/wish. I know that this is kind of a common problem, but I've never seen this case in particular where you need another value in addition to the one you're grouping by. I guess using the Window functions could help, but we're using MariaDB 10.0, so that's not really an option. I tried different subselect and joins but it always ends with the problem that I can't use the order_id to join, since I have to group by the client_id. It also came to my mind to join using the client_id AND order_timestamp but the combination is not unique in the table, since it's possible to have orders with the exact same (Unix) timestamp for one client or client/buyer combination (so yeah, this would be an edge case, I would need the buyer of the order with the higher order_id, but that's a problem for another day I guess).

If the table was filled like

| order_id | client_id | order_timestamp | buyer_id  |
|     1    |     123   |     9876543     |    2      |
|     2    |     123   |     9876654     |    3      |
|     3    |     234   |     9945634     |    2      |
|     4    |     234   |     9735534     |    1      |

I would like to get

| client_id | buyer_id |
------------|----------|
|     123   |    3     |
|     234   |    2     |

Hopefully, somebody can help me, so I can go to sleep in peace tonight.

Answer 1

If your MariaDB version supports window functions you can use ROW_NUMBER():

select t.client_id, t.buyer_id
from (
  select *,
    row_number() over (partition by client_id order by order_timestamp desc, order_id desc) rn
  from tablename
) t
where t.rn = 1

See the demo.
Results:

| client_id | buyer_id |
| --------- | -------- |
| 123       | 3        |
| 234       | 2        |

Without window functions use NOT EXISTS:

select t.client_id, t.buyer_id
from tablename t
where not exists (
  select 1 from tablename
  where client_id = t.client_id 
  and (
    order_timestamp > t.order_timestamp 
    or (order_timestamp = t.order_timestamp and order_id > t.order_id)
  )  
)

Answer 2

If you use max(field), it will pickup the first column of the group condition. In your case first occuring client_id per group which is not what you want.

Try this.

select client_id, order_timestamp, buyer_id from t
  where order_timestamp= 
     (select max(ot) from t as tcopy where tcopy.client_id= t.client_id ) 
  group by client_id;