Select Max Column - Query

[oval]

This is probably a general SQL question but since I am dealing w/ DB2 I figured I would ask here.

I have a table with customer transaction information. The primary keys are cust_id and date (transaction data that is). The trasactions can be later modified and this causes for the field TRANS_SQ to auto increment. What I want to do is query for the rows that pertain to a particular transaction but hold the highest TRANS_SQ value.

Below is a sample from the table. Basically the result from the query should en up in the following rows being selected:

1, 4, 6, 10, 11 and 12

ROW_NUM CUST_ID SERVICE_DATE TRANS_TYPE TRANS_SQ X_VALUE
1 67850 1/31/2007 1 3 xxx
2 67850 1/31/2007 1 2 xxx
3 67850 1/31/2007 1 1 xxx
4 67850 2/28/2007 1 2 xxx
5 67850 2/28/2007 1 1 xxx
6 67850 3/31/2007 1 4 xxx
7 67850 3/31/2007 1 3 xxx
8 67850 3/31/2007 1 2 xxx
9 67850 3/31/2007 1 1 xxx
10 68749 8/31/2006 1 1 xxx
11 68749 9/30/2006 1 1 xxx
12 68749 10/1/2006 1 1 xxx


Thanks in advance for your help. And sorry for the ill formatted table.

Oscar

[MMcCarthy]

Try this ...

SELECT First(ROW_NUM], CUST_ID, SERVICE_DATE, TRANS_TYPE, Max(TRANS_SQ), X_VALUE
FROM TableName
GROUP BY CUST_ID, SERVICE_DATE, TRANS_TYPE,  X_VALUE