SQL How to Select the Most Recent Date Item

SQL: Select most recent date for each category

SELECT
    category_a,
    category_b,
    MAX(date)
FROM
    Some_Unnamed_Table
GROUP BY
    category_a,
    category_b
ORDER BY
    category_a,
    category_b

I certainly don't mind helping people when I can, or I wouldn't be on this site, but did you really search for an answer for this before posting?

How do I select the rows with the most recent date in SQL?

Perhaps you seems want :

select d.employee_id, d.status, d.update_date 
from data d
where update_date = (select max(d1.update_date) 
                     from data d1 
                     where d1.employee_id = d.employee_id
                    );

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)

SQL Server Selecting Records with most recent date time

First combine the date + time columns into a datetime so it's easy to order them. It's been a while since I used Sql Server, but the row_number() function and partitioning is an easy way to find the max of one column grouped by another - the partition clause is similar to a group by.

select t.* 
from
(
    select t.MyKey, t.MyDateTime
    , row_number() over 
         (partition by t.mykey order by t.MyDateTime desc) as keyOrderNbr
    from table t
) A
inner join table t
    on A.MyKey = t.MyKey 
    and A.MyDateTime = t.MyDateTime
where A.keyOrderNbr = 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

---

Related Topics