Select a single row from two distinct columns

Question

Possible Duplicate:
How to get the MAX row

I need to fetch the "latest" row for certain conditions in my tkhist1 table. Tickets are bound to TicketNo and LocationID, and I need to select one row for DISTINCT values of both of those columns.

Multiple rows may share the same TicketNo and LocationID though, they are additionally defined by a UniqueID column. I need to select a single row for each combination of TicketNo and LocationID, SELECTING the row with the single highest UniqueID value. I do not need Unique ID in my SELECT statement.

The following query selects some of the information I need, but it does not only grab one row per UniqueID. If a row has different values for FreightPay (or any other column) it will be SELECTED as a new row.

SELECT DISTINCT
    [TicketNo],
    [LocationID],   
    [FreightPay]
  FROM [Tkhist1]

I need a single row for each distinct pair of TicketNo and LocationID, selecting only information from the highest value (DESC) UniqueID

score 1 · Accepted Answer · answered Nov 17 '11 at 17:45

Just INNER JOIN on a subquery:

SELECT  t1.[TicketNo],
        t1.[LocationID],   
        t1.[FreightPay]
FROM [Tkhist1] t1
INNER JOIN (
                SELECT TicketNo,
                       LocationID,
                       MAX(UniqueID) UniqueID
                FROM Tkhist1
                GROUP BY TicketNo, LocationID
             ) sub
    ON sub.UniqueID = T1.uniqueId

Select a single row from two distinct columns

1 Answers1