I have a Join that's creating identical rows except for an incremental
column.  I want to only return the row with the highest incremental
number, how can I accomplish this?

DB structure is like

EP_KEY(INT), PAT_KEY(INT), PAT_NAME(VARCHAR), EPISODE(INT)

The EP_KEY and PAT_KEY are the primary key, the EPISODE is the
incremental column, data might look like:

1    1    John Doe      1
1    1    John Doe      2
1    1    John Doe      3


I only want to return

1    1    John Doe      3

Not sure what the key columns are. Anyway, do:

SELECT *
  FROM tbl t1
WHERE t1.episode = ( SELECT MAX( t2.episode )
                        FROM tbl t2
                       WHERE t2.pat_name = t1.pat_name ) ;

On 16 Feb 2006 13:46:10 -0800, jrmann1***@gmail.com wrote:

Show quoteHide quoteHi jrmann1999,

SELECT   EP_Key, Pat_Key, Pat_Name, MAX(Episode)
FROM     (insert your query here)
GROUP BY EP_Key, Pat_Key, Pat_Name

Need Help Please...
How to compare strings for equality in Transact SQL
Why does doing update in chunks works faster than doing the whole?
trigger Performance question
Unique index on joined view for fulltext index