SQL - Most Recent Time Stamp for Each User

Question

I have a table with a user Account,the current status of the account and the Timestampe of any changes made to the status. When i run the following command for 2 specific accounts it brings back every date & time a change has occured but i only need the most recent date and time for each account.

When i try to include a Group BY ACCOUNT i get the following error:

"must be GROUPed or used in an aggregate function"

Any assistance would be much appreciated.

select ACCOUNT, Status,LAST_UPDATE_TIMESTAMP
from ACCOUNT_ROLE 
where ACCOUNT in (123456789,987654321);

Se [here](https://stackoverflow.com/questions/tagged/greatest-n-per-group+sql) — , Jun 26 '18 at 13:47
Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Jun 26 '18 at 13:47

score 2 · Answer 1 · answered Jun 26 '18 at 13:52

2

If you want to group by ACCOUNT and get the maximum last update timestamp for each account you could try the following SQL:

select ACCOUNT, MAX(LAST_UPDATE_TIMESTAMP)
    from ACCOUNT_ROLE 
    where ACCOUNT in (123456789,987654321) -- this is an optional filter clause.
    group by ACCOUNT

You will have to leave out the STATUS field from the select clause.

Assumptions: LAST_UPDATE_TIMESTAMP is datetime type field.

answered Jun 26 '18 at 13:52

gil.fernandes

12,978
5
63
76

Thats correct, LAST_UPDATE_TIMESTAMP is the date a change occurred, at the moment with my current code every date is displayed. – P.4001 Jun 26 '18 at 14:00

score 0 · Answer 2 · answered Jun 26 '18 at 13:52

0

I don't know why you are using GROUP BY clause. i think u are trying to display values by ordering of ACCOUNT. If so user Order by ACCOUNT

answered Jun 26 '18 at 13:52

Manju

34
6

select ACCOUNT, Status,LAST_UPDATE_TIMESTAMP from ACCOUNT_ROLE where ACCOUNT in (123456789,987654321) ORDER BY ACCOUNT; – Manju Jun 26 '18 at 13:54
Is there a way to reduce the output to the most recent date ? rather than displaying every date a change occurred for each Account. Example output below: 123456789 ACTIVE 2012-07-07 12:39:07 123456789 ACTIVE 2018-05-21 06:10:47 123456789 ACTIVE 2018-05-23 06:53:16 123456789 ACTIVE 2018-05-23 06:53:16 987654321 ACTIVE 2013-01-25 12:42:53 987654321 NEVER 2012-05-24 07:35:34 987654321 ACTIVE 2012-05-26 08:56:46 987654321 ACTIVE 2017-03-23 07:08:48 – P.4001 Jun 26 '18 at 14:04

SQL - Most Recent Time Stamp for Each User

2 Answers2