CODEWITHSUNDEEP
sqlsql-serverdatetimeselectmax
user2571510
user2571510

Reputation: 11377

SQL Server: Get data from record with max date / newest date

I am using the following stored procedure to get the newest date out of a column which works fine.

Can someone tell me how I have to amend this that I also get the rest of the data of this record selected (i.e. the one with the max date) ? The column in question is called "updated" and is formatted as datetime so the values there should be unique.

Example: The max value for column "updated" is the row with "itemID" = 3. In this case I also want the rest of the data in this row selected, say the columns are called col1, col2, col3, col4 + the column "updated".

My stored procedure: 

SET NOCOUNT ON;
SELECT      CONVERT(VARCHAR(11), MAX(updated), 106) AS lastUpdated
FROM        MeetingDetails
WHERE       itemStatus = 'active'
FOR XML PATH('updates'), ELEMENTS, TYPE, ROOT('root')

Upvotes: 0

Views: 9906

Answers (4)

Jon Senchyna
Jon Senchyna

Reputation: 8037

A simple TOP 1 clause with an ORDER BY should work for you.

SELECT TOP 1 col1, col2, col3, col4,
       updated
FROM   @MeetingDetails 
WHERE  itemStatus = 'active'
ORDER BY
      updated DESC
FOR XML PATH('updates'), ELEMENTS, TYPE, ROOT('root')

Upvotes: 1

Mureinik
Mureinik

Reputation: 311073

You could take the analytic approach:

SELECT *
FROM   (SELECT col1, col2, col3, col4, 
               RANK() OVER (PARTITION BY col1, col2, col3 
                            ORDER BY updated DESC) AS rk
        FROM   MeetingDetails
        WHERE  itemstatus = 'active') t
WHERE  rk = 1

Upvotes: 3

Consult Yarla
Consult Yarla

Reputation: 1150

Here is the query:

set nocount on;

select 
   md.col1, 
   md.col2,
   -- ......
   convert(varchar(11), md.updated, 106) as lastUpdated
from
   MeetingDetails md
where
   md.itemStatus = 'active'
   and md.updated = (select max(updated)
                     from MeetingDetails
                     where itemStatus = 'active')
for xml path('updates'), elements, type, root('root')

Based on how many records have the max(updated) this select may return more than one row and hence this query could return multiple nodes.

Upvotes: 1

John
John

Reputation: 186

SELECT col1, col2, col3, col4, updated
FROM   MeetingDetails 
WHERE  updated in (
    SELECT      MAX(updated)
    FROM        MeetingDetails
    WHERE       itemStatus = 'active'
)

This should be what you're looking for. From here, if you need to narrow it down further, just add more criteria to your where clause.

Upvotes: 1

Related Questions