gattoo gattoo - 4 months ago 8
SQL Question

Get last entry from each user in database

I have a Postgresql database, and I'm having trouble getting my query right, even though this seems like a common problem.

My table looks like this:

CREATE TABLE orders (
account_id INTEGER,
order_id INTEGER,
ts TIMESTAMP DEFAULT NOW()
)


Everytime there is a new order, I use it to link the
account_id
and
order_id
.

Now my problem is that I want to get a list that has the last order (by looking at
ts
) for each account.

For example, if my data is:

account_id order_id ts
5 178 July 1
5 129 July 6
4 190 July 1
4 181 July 9
3 348 July 1
3 578 July 4
3 198 July 1
3 270 July 12


Then I'd like the query to return only the last row for each account:

account_id order_id ts
5 129 July 6
4 181 July 9
3 270 July 12


I've tried
GROUP BY account_id
, and I can use that to get the
MAX(ts)
for each account, but then I have no way to get the associated
order_id
. I've also tried sub-queries, but I just can't seem to get it right.

Thanks!

Answer
select distinct on (account_id) *
from orders
order by account_id, ts desc

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT:

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

Comments