How to Query SQL For a Latest Record Date For Each User

Selecting max record for each user

This solution uses the uniqueness of the ContractId field:

SELECT MemberID, ContractID, StartDate, EndDate
FROM member_contracts 
WHERE ContractId IN (
    SELECT MAX(ContractId)
    FROM member_contracts 
    GROUP BY MemberId
)

See it working online: sqlfiddle

how do I query sql for a latest record date for each user

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

Select row with most recent date per user

Query:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

Result:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Note that if a user has multiple records with the same "maximum" time, the query above will return more than one record. If you only want 1 record per user, use the query below:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

How to get the latest date of entry for each user in Sqlite?

You can use max(date) to get the most recent date for each user.

SELECT
  user_id,
  MAX(date)
FROM answers
GROUP BY user_id,
ORDER BY date DESC;

how do I query sql for a latest record date and hour for each user

It is not clear why date and time are two separate columns. Maybe because they can be null for certain scenarios. So get those records for which both are given (and presumably belong together), combine them to one datetime and work with this.

select 
  id_processo,
  max(timestamp(data_andamento, hora_andamento))
from andamento_processo
where data_andamento is not null
  and hora_andamento is not null
group by id_processo
order by id_processo;

If you want more data from the records with the max datetimes then use above as a subquery (derived table) and join the table again.

For completeness sake: Here the same query in an IN clause to get more data from the records:

select *
from andamento_processo
where (id_processo, timestamp(data_andamento, hora_andamento)) in
(
  select 
    id_processo,
    max(timestamp(data_andamento, hora_andamento))
  from andamento_processo
  where data_andamento is not null
    and hora_andamento is not null
  group by id_processo
)
order by id_processo;

---

Related Topics