Post reply

Return only one record

  • February 2, 2009 at 2:08 pm

    #196774

    190053 2008-07-07 00:00:00 1.019.37.1.264

    190053 2009-01-05 00:00:00 1.019.37.1.260 (display just this one as it max date)

    I am tyring to just return the empl id and the record that has 2009-01-05 date

    SELECT distinct EMPL_ID, max(effective_dt), ORG FROM ORG

    GROUP BY EMPL_ID , ORG

    But i get both records.

    Any one see what im doing wrong.

  • February 2, 2009 at 2:16 pm

    #936744

    WITH CTE AS (

    SELECT EMPL_ID, effective_dt, ORG,

    ROW_NUMBER() OVER(PARTITION BY EMPL_ID ORDER BY effective_dt DESC) AS rn

    FROM ORG)

    SELECT EMPL_ID, effective_dt, ORG

    FROM CTE

    WHERE rn=1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 2, 2009 at 2:39 pm

    #936753

    impressive ........it worked, can you explain the logic it is doing not used this before. OVER PARTITION

  • February 2, 2009 at 4:00 pm

    #936787

    According to the online help for the OVER clause:

    PARTITION BY

    Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

    So, basically the count starts over again at each EmpID.

    -------------------------------------------------------------------

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/ddcef3a6-0341-43e0-ae73-630484b7b398.htm

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • February 2, 2009 at 4:35 pm

    #936799

    Sure is easy when you know how......thanks guys i appreciate your quick turn around too.

    Have a good night.

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

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