gattoo gattoo - 1 year ago 60
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:

account_id INTEGER,
order_id INTEGER,

Everytime there is a new order, I use it to link the

Now my problem is that I want to get a list that has the last order (by looking at
) 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
for each account, but then I have no way to get the associated
. I've also tried sub-queries, but I just can't seem to get it right.


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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download