Post reply

Select top 1 from subquery

  • December 5, 2008 at 10:17 am

    #71107

    Good afternoon. I have a table (let's call it tblname) with rows in it in the following format.

    person (uniqueidentifer), id (int), dttm (datetime)

    Here's some test data.

    xxx-xxx..|6|'12/08/2007'

    xxx-xxx..|9|'12/01/2008'

    I run the following query, expecting to get 9, but I get 6.

    select top 1 id from (select id,max(dttm) as LastJobDate

    from tblname

    where tblname.person = 'xxx-xxx...'

    group by id order by lastjobdate desc) as tmp)

    Anyone know why? What am I missing? Any help is greatly appreciated.

    Thanks...Chris

  • December 5, 2008 at 10:45 am

    #908512

    Does this work?

    select top 1 id

    from tblname

    where tblname.person = 'xxx-xxx...'

    group by id order by max(dttm) desc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 5, 2008 at 10:59 am

    #908527

    It does...thank you VERY much!

    Chris

  • December 5, 2008 at 11:45 am

    #908576

    GilaMonster (12/5/2008)

    Does this work?

    select top 1 id

    from tblname

    where tblname.person = 'xxx-xxx...'

    group by id order by max(dttm) desc

    Just outta curiosity, doesn't the above make the TOP unnecessary, or is there more than 1 ID per person? If person is truly a uniqueidentifer shouldn't just the fact that you are selecting person in your where statement get you the desired 1 id, and 1 date that you are looking for?

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • December 5, 2008 at 11:56 am

    #908587

    Yes, there is more than 1 ID per person. I only wanted the most recent ID. Gail's post helped me do that. Thanks.

    Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply