How to get last rows?

Question

In the above table, I want to get rows where id=7,8,9

SELECT * FROM order GROUP BY user_id ORDER BY pay_time DESC

returns id=1,2,3

How do I get the expected result?

Answer 1

I think ROW_NUMBER is what you want here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY pay_time DESC) rn
    FROM yourTable
)

SELECT id, user_id, pay_time
FROM cte
WHERE rn = 1;

The old fashioned way of doing this uses a join to a subquery which finds the most recent pay times for every user:

SELECT t1.id, t1.user_id, t1.pay_time
FROM yourTable t1
INNER JOIN
(
    SELECT user_id, MAX(pay_time) AS max_pay_time
    FROM yourTable
    GROUP BY user_id
) t2
    ON t2.user_id = t1.user_id AND
       t2.max_pay_time = t1.pay_time;