DB2 Show the latest row record from users, using the date and time

Question

I have 100 records from 3 users. I want to show the most recent record from each user. I have the following query:

SELECT *  
FROM Mytable  
WHERE Dateabc = CURRENT DATE 
AND timeabc =  
(
  SELECT MAX(timeabc)
  FROM Mytable
)

It returns the most recent record for everyone, and I need it to return most recent record from every user.

Answer 1

Should the solution support both DB2 and mysql?

SELECT * FROM Mytable as x
WHERE Dateabc = CURRENT_DATE 
  AND timeabc = (SELECT MAX( timeabc ) FROM Mytable as y where x.user = y.user)

If it's only DB2 more efficient solutions exists:

SELECT * from (
    SELECT x.*, row_number() over (partition by user order by timeabc desc) as rn 
    FROM Mytable as x
)
WHERE rn = 1

Answer 2

I assume somewhere in your table you have a userID...

  select userID, max(timeabc) from mytable group by userID

Answer 3

SELECT *
FROM Mytable as a
WHERE Dateabc = CURRENT_DATE 
AND timeabc =
(
    SELECT MAX( timeabc )
    FROM Mytable as b 
    WHERE a.uId = b.uId
)