Create a SQL Query to Retrieve Most Recent Records

Create a SQL query to retrieve most recent records

Aggregate in a subquery derived table and then join to it.

 Select Date, User, Status, Notes 
    from [SOMETABLE]
    inner join 
    (
        Select max(Date) as LatestDate, [User]
        from [SOMETABLE]
        Group by User
    ) SubMax 
    on [SOMETABLE].Date = SubMax.LatestDate
    and [SOMETABLE].User = SubMax.User 

Select most recent record based on date

You could try the following query:

SELECT TOP 1 *
FROM Table
ORDER BY [Date] DESC

SQL Get Only Most Recent Record for A User

My favorite way of doing this is using Row_Number() which will number each row based upon the criteria you set - In your case, you'd partition by U.UserName since you want one row returned for each user and order by R.Created DESC to get the latest one.

That being the case, you'd only want to get back the rows that have RN=1, so you query that out as follows:

WITH cte AS
(
    SELECT
     ROW_NUMBER() OVER(PARTITION BY U.UserName ORDER BY R.Created DESC) AS RN
     ,U.UserName, C.Name AS 'ClassroomName', U.FirstName, U.LastName, L.Name AS 'LessonName', P.PageNumber, R.Attempt, R.Created
    FROM Responses R
    INNER JOIN ClassroomUsers CU ON CU.UserId = R.UserId
    INNER JOIN Classrooms C ON C.Id = CU.ClassroomId
    INNER JOIN Questions Q ON Q.Id = R.QuestionId
    INNER JOIN Pages P ON P.Id = Q.PageId
    INNER JOIN Lessons L ON L.Id = P.LessonId
    INNER JOIN AspNetUsers U ON U.Id = CU.UserId
    WHERE CU.ClassroomId IN (
        SELECT CU.ClassroomId
        FROM ClassroomUsers CU
        WHERE CU.UserId = @UserId
    )
)


SELECT * FROM cte WHERE RN = 1

Hope that makes sense / helps!!

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

Get most recent row for given ID

Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id.

SELECT 
 id, 
 MAX(signin) AS most_recent_signin
FROM tbl
GROUP BY id

To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id.

SELECT 
  tbl.id,
  signin,
  signout
FROM tbl
  INNER JOIN (
    SELECT id, MAX(signin) AS maxsign FROM tbl GROUP BY id
  ) ms ON tbl.id = ms.id AND signin = maxsign
WHERE tbl.id=1

T-SQL Query: Select only the most recent row (for each computer name)

You can do this by using RowNumber which gives a row number to every row. Since you want to give row number starting from 1 for each computerName based on TimeStamp, we need to partition it by Computername

select * from (
select *, ROW_NUMBER() over (partition by ComputerName order by Timestamp desc) as rno 
from ComputerStatus
) as T where rno = 1

MSDN

How to get the most recent records from MySQL?

I kept with this one:

SELECT DISTINCT r1.url FROM page_report r1
    LEFT JOIN page_report r2 ON r1.url = r2.url AND r1.created_date < r2.created_date
    WHERE r2.url IS NULL;

Get most recent record from Right table with sub query

When you perform a join, you attach all records from the table that match your join conditions.

If the table is indexed appropriately, a subquery could achieve the goal of obtaining the most recent value, however, if the query uses the wrong index, TOP or equivalent functions may return the wrong result.

There are a number of ways to accomplish the task of retrieving the most recent record but they are contingent on a couple of things.

Firstly, you need to be able to identify what the most recent row is, usually by a column called CreatedDate or something similar against the IDs. (You should know what that business logic is, it may be that the table is chronologically entered [as most tables are] and therefore, SubID might be a thing. We're going to assume it is CreatedDate.)

Secondly, you need to rank the rows in terms of the CreatedDate in a descending order so that the newest matching ID is ranked 1.

Finally, you filter your results by 1 to return the newest result, but you could also filter by <= x if you are interested in the top x newest return results per ID.

To use more mathematical language: We are deriving a value from the CreatedDate and ID values and then using that derivative value to sort and filter the data. In this case we are deriving the RowNumber from the CreatedDate in descending order for each ID.

In order to accomplish this, you can use the Windowed Function ROW_NUMBER(),

ROW_NUMBER() OVER (PARTITION BY id ORDER BY CreatedDate DESC) as RankID

This windowed function will return a row value for each ID relative to the CreatedDate in descending order, where the newest created date is equal to 1.

You can then put brackets around the whole query to make it into a table so you will be able to filter the results of that Windowed Function.

SELECT id, txt
    (SELECT id, txt
     ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY CreatedDate DESC) as RankID
      FROM SourceTable) A
WHERE RankID = 1

This should achieve your goal of returning the "newest result".

What ever your column is that determines the age of the data relative to the ID, it can be multiple, should be placed within the ORDER BY.

In order to make this query perform faster, you should index your data appropriately, whereby ID is the the first column, and CreatedDate Desc is your next column. This means your system will not have to perform a costly sort every time this runs, but that depends on whether you plan on using this query often and how much overhead it is grabbing.

---

Related Topics