Post reply

How to determine earliest date for a group of records

  • January 11, 2010 at 8:54 am

    #96145

    Hi,

    I'm trying to determine the earliest start date for a set of records. Here is what the records look like:

    Column1 Column 2 Column3

    1000007323 002666 1997-10-16

    1000007323 005846 1998-12-23

    1000007323 008038 1998-12-23

    1000007323 010046 2000-02-01

    1000007323 040341 1991-04-01

    1000007323 071634 1990-05-01

    Now, I know I can do a MIN(Column3) and group by Column1 and it will return a date of 1990-05-01 for the value in Column1. What I really want returned back is the earliest start date(column3) but what value in column2 it is attached to - for example 071634 has the earliest start date for the value in Column1. If I group by Column2 it will return back all columns because thay all satisfy the mininum.

    Any help would be appreciated.

  • January 11, 2010 at 9:02 am

    #1101866

    For future reference, please read through this for the best way to post to get fast replies. http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Now this isn't tested, cause no table structure was posted, but should work with minimal tweaking.

    Select Col1, Col2, Col3

    FROM

    (SELECT Col1, Col2, Col3, Row_number() Over (Partition by Col1 Order By Col3) As RowNo

    FROM SomeTable) Sub

    WHERE RowNo = 1

    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

  • January 11, 2010 at 9:30 am

    #1101891

    You can try something like this. With this query if there are two records with same minimum date then it will return two records

    select table1.*

    from table1,

    ( select min(column3) column3

    from table1

    ) x

    where table1.column3=x.column3

    Here table1 is assumed as name of the table having data.

    See if it works for you.

  • January 11, 2010 at 10:09 am

    #1101922

    Yes that did the trick. Thanks ever so much.

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

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