Home > IT Answers > SQL Server > Filtering a select based on the value of one ...
CO Bill
120 pts.
 Filtering a select based on the value of one column and the max value of another column
SELECT, Temp Tables
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
ASKED: Jan 25, 2012  6:07 PM GMT
UPDATED: February 28, 2012  11:08:12 AM GMT
RELATED QUESTIONS
Carlosdl
60,245 pts.
 
  Help
 Approved Answer - Chosen by CO Bill (Question Asker)
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:

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';


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';
ANSWERED:  Jan 25, 2012  6:57 PM (GMT)  by Carlosdl   60,245 pts.
 
Other Answers:
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:

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';


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';
Last Wiki Answer Submitted:  Jan 25, 2012  6:57 PM (GMT)  by  Carlosdl   60,245 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

[...] Carlosdl has the answer about filtering a select based on the value of one column and the maximum value of another column. .topsy_widget_shortcode,div.topsy-sm {display:inline;} .post {padding-left: 10px;}   [...]

The Most-Watched IT Questions: February 6, 2012 - ITKE Community Blog
 0 pts.