Filtering a select based on the value of one column and the max value of another column

120 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CO Bill
    [...] 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;}   [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following