locked
Return one record for each SalesID RRS feed

  • Question

  • Hi,

    I have a SalesItem table that can have multiple records with the same SalesID. How do I query this table to show only one record per SalesID? I suppose I'm looking to return something like the first record for each SalesID.

    Cheers

    Paul

    Monday, July 22, 2013 2:04 PM

Answers

  • You need to decide which record you would like to choose, maybe according to some other column, like SalesDate or similar. In genera, you can to use ROW_NUMBER() function

    SELECT SalesID, other columns

    FROM (

    SELECT SalesID, other columns,

         rn = ROW_NUMBER() OVER ( PARTITION BY SalesID ORDER BY someothercol ) 

    FROM yourtable

    )t

    WHERE rn = 1

     
    Monday, July 22, 2013 2:15 PM