Select Latest Records by Datetime Field

Select latest record in table (datetime field)

SELECT 
    id, thread_id, user_id, subject, body, date_sent
FROM
    messages
WHERE
    date_sent IN (SELECT 
            MAX(date_sent)
        FROM
            messages
        WHERE
            user_id = 6
        GROUP BY thread_id)
ORDER BY thread_id ASC, date_sent DESC;

Let me know if it works now

Selecting the latest record based on DATETIME

The simplest is using a ranking function like ROW_NUMBER:

WITH CTE AS
(
   SELECT MemberID, CreatedDate, 
          RN = ROW_NUMBER() OVER (PARTITION BY MemberID 
                                  ORDER BY CreatedDate DESC)
   FROM dbo.TableName  
)
SELECT MemberID, CreatedDate FROM CTE WHERE RN = 1

This just returns one record per MemberID (the one with the latest CreatedDate). If you have multiple latest records an arbitrary is taken. If you want to take all in this case you have to replace ROW_NUMBER with DENSE_RANK.

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

This is the simple old school approach that works with almost any db engine, but you have to watch out for duplicates:

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

Using window functions will avoid any possible issues with duplicate records due to duplicate date values, so if your db engine allows it you can do something like this:

select x.username, x.date, x.value 
from (
    select username, date, value,
        row_number() over (partition by username order by date desc) as _rn
    from MyTable 
) x
where x._rn = 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

SQL get the last date time record

If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend:

select filename ,
       status   ,
       max_date = max( dates )
from some_table t
group by filename , status
having status = '<your-desired-status-here>'

Easy!

how can i get the latest record in database based on datetime by comparing 2 tables

Following query would give you desired result:

SELECT * FROM 
    (
    SELECT  code, name , environment, result , [date], [time]
            ROW_NUMBER() OVER (PARTITION BY environment, Code ORDER BY [Date] desc, [Time] desc) rn
    FROM table1 t1
    LEFT JOIN table2 t2 ON t1.code=t2.code
    ) AS T 
Where T.rn = 1

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)

---

Related Topics