Select From Select... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select From Select...

Author

Topic

waldis
Starting Member

21 Posts

Posted - 2011-10-18 : 20:38:20

Hi,

I want to get all records where SID = 2, and within those records find a single record that has the latest date in DateUpdated field. This is what I have come up with:


SELECT S1.PID,
       S1.UID,
       S1.MID,
       S1.SID,
       S1.DateCreated,
       S1.DateUpdated
FROM (SELECT [Table1].PID,
             [Table1].UID,
             [Table1].MID,
             [Table1].SID,
             [Table1].DateCreated,
             [Table1].DateUpdated
      FROM [Table1]
      WHERE [Table1].SID = 2
     ) as S1
WHERE S1.DateUpdated = (SELECT max(S2.DateUpdated)
                        FROM (SELECT [Table1].PID,
                                     [Table1].UID,
                                     [Table1].MID,
                                     [Table1].SID,
                                     [Table1].DateCreated,
                                     [Table1].DateUpdated
                              FROM [Table1]
                              WHERE [Table1].SID = 2
                             ) as S2
                       )

Is there a way to do the same in a more efficient manner, or is this as short as it gets?

Thank you,

Val

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-18 : 20:46:58

Does this meet your requirements?[CODE]select a.*
from (
SELECT
PID,
UID,
MID,
SID,
DateCreated,
DateUpdated,
ROW_NUMBER() over (order by DateUpdated DESC) rn
FROM Table1
WHERE SID = 2
) a
where rn = 1[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

waldis
Starting Member

21 Posts

Posted - 2011-10-19 : 13:14:35

That's clever! And instead of using max() you just sort the DateUpdated DESC and select the first, top row.

Thanks very much for your input.

Val

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-19 : 13:57:57

I saw your post and got called away from my desk, but since I had it I'll post it even though Bustaz solution works just fine:

SELECT TOP 1
    PID,
    UID,
    MID,
    SID,
    DateCreated,
    DateUpdated,
FROM 
    Table1
WHERE 
    SID = 2
ORDER BY
    DateUpdated DESC

waldis
Starting Member

21 Posts

Posted - 2011-10-19 : 16:57:55

Thanks for another solution.

Val

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources