SQL query to get multiple rows with different timestamps-CodePudding

I am attempting get some data with their latest timestamps. sets of data have different timestamps which i need to get using their timestamps. for example -

id  |  testcase_id  |  user     |  timestamp
-------------------------------------------------------
1   |  2            |  abbc     |  2013-01-13 15:00:00
-------------------------------------------------------
2   |  2            |  pbbb     |  2013-01-13 15:05:00
-------------------------------------------------------
3   |  4            |  dddd     |  2013-01-13 15:05:00
-------------------------------------------------------
4   |  4            |  abbc     |  2010-01-10 16:04:00

For abbc need to get row1 which have latest timestamp(2013-01-13 15:00:00) and i need row 2 with pbbb (2013-01-13 15:05:00) and row 3 dddd(2013-01-13 15:05:00). I have this timestamp data for each user in backend just need a proper query to fetch all those rows using timestamps.

Note: There could be multiple users with same timestamps, ex - if 10 users have same timestamp as abbc i will require all those users.

CodePudding user response：

JPQL is picky about the syntax it allows. You may try using correlated subqueries here:

select e
from your_entity e
where e.timestamp = (select max(f.timestamp)
                     from your_entity f where f.user = e.user)

CodePudding user response：

Try CTE analytical function row_number()

with t1 as (
  select id, testcase_id, user, timestamp, 
    row_number() over (partition by user order by timestamp desc) rno
  from your_table)
select select id, testcase_id, user, timestamp
from t1
where rno = 1