Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    29

    Unanswered: SQL Query in SQL 2005

    I have following data and wanted to find out how I can tag the records.

    Date ConfirmationNumber SequenceNum
    03/09/2010 A0001 1
    03/09/2010 A0001 2
    03/09/2010 A0001 3
    03/09/2010 A0002 4
    03/09/2010 A0002 5
    03/09/2010 A0002 6

    Here is what I need. How Can I query or tag above so I can just get the first records such as following.
    03/09/2010 A0002 4
    03/09/2010 A0002 4

    your help is greatly apreciated.

    Kiran

  2. #2
    Join Date
    Feb 2003
    Posts
    29
    sorry I made mistake

    Here is what I need. How Can I query or tag above so I can just get the first records such as following.

    03/09/2010 A0001 1
    03/09/2010 A0002 4

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    ....enumerationalised = ROW_NUMBER() OVER(PARTITION BY Date, ConfirmationNumber ORDER BY SequenceNum)
    Stick that in your select clause. You should now find your required rows are both numbered "1". Just filter on that.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try min() and group by.
    Dave

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ....or that. Dave's solution is better if there are no other columns to return, otherwise windows aggregates are easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •