Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

marked as duplicate by jcolebrand Nov 17 '11 at 20:15

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.