CODEWITHSUNDEEP
sqlsql-servergroup-by
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Returning most recent row SQL Server

I have this table

CREATE TABLE Test (
OrderID int,
Person varchar(10),
LastModified Date
);


INSERT INTO Test (OrderID, Person, LastModified)
VALUES (1,  'Sam', '2018-05-15'),
(1,  'Tim','2018-05-14'),
(1,  'Kim','2018-05-05'),
(1,  'Dave','2018-05-13'),
(1,  'James','2018-05-11'),
(1,  'Fred','2018-05-05');

select * result:

| OrderID | Person | LastModified |
|---------|--------|--------------|
|       1 |    Sam |   2018-05-15 |
|       1 |    Tim |   2018-05-14 |
|       1 |    Kim |   2018-05-05 |
|       1 |   Dave |   2018-05-13 |
|       1 |  James |   2018-05-11 |
|       1 |   Fred |   2018-05-05 |

I am looking to return the most recent modified row which is the first row with 'Sam'.

Now i now i can use max to return the most recent date but how can i aggregate the person column to return sam?

Looking for a result set like 

| OrderID | Person | LastModified |
|---------|--------|--------------|
|       1 |    Sam |   2018-05-15 |

I ran this:

SELECT 
OrderID,
max(Person) AS [Person],
max(LastModified) AS [LastModified]
FROM Test
GROUP BY 
OrderID

but this returns:

| OrderID | Person | LastModified |
|---------|--------|--------------|
|       1 |    Tim |   2018-05-15 |

Can someone advice me further please? thanks

*** UPDATE

INSERT INTO Test (OrderID, Person, LastModified)
VALUES (1,  'Sam', '2018-05-15'),
(1,  'Tim','2018-05-14'),
(1,  'Kim','2018-05-05'),
(1,  'Dave','2018-05-13'),
(1,  'James','2018-05-11'),
(1,  'Fred','2018-05-05'),
(2,  'Dave','2018-05-13'),
(2,  'James','2018-05-11'),
(2,  'Fred','2018-05-05');

So i would be looking for this result to be:

| OrderID | Person | LastModified |
|---------|--------|--------------|
|       1 |    Sam |   2018-05-15 |
|       2 |   Dave |   2018-05-13 |

Upvotes: 2

Views: 130

Answers (5)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Here is one other method :

select t.*
from Test t
where LastModified = (select max(t1.LastModified) from Test t1 where t1.OrderID = t.OrderID);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

How about just using TOP (1) and ORDER BY?

SELECT TOP (1) t.*
FROM Test t
ORDER BY LastModified DESC;

If you want this for each orderid, then this is a handy method in SQL Server:

SELECT TOP (1) WITH TIES t.*
FROM Test t
ORDER BY ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY LastModified DESC);

Upvotes: 1

xcvd
xcvd

Reputation: 696

If you always want just one record (the latest modified one) per OrderID then this would do it:

SELECT
      t2.OrderID
    , t2.Person
    , t2.LastModified

FROM (
    SELECT
          MAX( LastModified ) AS LastModified
        , OrderID

    FROM 
        Test

    GROUP BY
        OrderID
) t

INNER JOIN Test t2
    ON t2.LastModified = t.LastModified
    AND t2.OrderID = t.OrderID

Upvotes: 4

Thom A
Thom A

Reputation: 95544

Expanding on your comment ("thanks very much, is there a way i can do this if there is more than one orderID e.g. multiple people and lastmodified for multiple orderID's?"), in xcvd's answer, I assume what you therefore want is this:

WITH CTE AS(
    SELECT OrderId,
           Person,
           LastModifed,
           ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY LastModified DESC) AS RN
    FROM YourTable)
SELECT OrderID,
       Person,
       LastModified
FROM CTE
WHERE RN = 1;

Upvotes: 1

haag1
haag1

Reputation: 352

"xcvd's" answer is perfect for this, I would just like to add another solution that can be used here for the sake of showing you a method that can be used in more complex situations than this. This solution uses a nested query (sub-query) to find the MAX(LastModified) regardless of any other field and it will use the result in the original query's WHERE clause to find any results that meet the new criteria. Cheers.

SELECT OrderID
    , Person
    , LastModified
FROM Test
WHERE LastModified IN (SELECT MAX(LastModified)
                       FROM Test)

Upvotes: 0

Related Questions