Filtering a select based on the value of one column and the max value of another column
I've created a temp table and need to further select some data from it. Seems simple. The temp table contains thousands of orders, an action code (C or D)and a seq_ID for each order. Each order has multiple instances of the C and D action codes, but I only want a list of those where the value for the greatest seq_id is a C. So from this example, I want to include SO_ID 4055 in the list, because the greatest SEQ_ID is associated with an action code of C, but I do not want to include SO_ID 4089 because the action code associated with the max SEQ_ID for that order is a D. Do I need a self-join? a having clause? both? Thanks.
SEQ_ID ACT_COD SO_ID 1981922 C 4055 1981924 D 4055 1981925 C 4055 1981926 C 4089 1981927 D 4089
Software/Hardware used:
SQLServer 2000
SEQ_ID ACT_COD SO_ID 1981922 C 4055 1981924 D 4055 1981925 C 4055 1981926 C 4089 1981927 D 4089
Software/Hardware used:
SQLServer 2000
ASKED:
January 25, 2012 6:07 PM
UPDATED:
February 28, 2012 11:08 AM
Answer Wiki:
You are probably going to need a sub-select.
Here are a couple of possible options, although the syntax from the first example might not be supported on your database platform:
<pre>SELECT *
FROM temp
WHERE (so_id,seq_id)
IN (SELECT so_id,MAX(seq_id) FROM temp GROUP BY so_id)
AND act_code = 'C';</pre>
<pre>SELECT t.*
FROM temp t
JOIN (SELECT so_id,MAX(seq_id) max_seq FROM temp GROUP BY so_id) t2
ON t.so_id = t2.so_id
AND t.seq_id = t2.max_seq
WHERE t.act_code = 'C';</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
