Get last entry from each user in database

gattoo Published at Dev

gattoo

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!

Clodoaldo Neto

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.