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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

  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.

up vote 1 down vote accepted

Just INNER JOIN on a subquery:

SELECT  t1.[TicketNo],
FROM [Tkhist1] t1
                SELECT TicketNo,
                       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.