SQL question using MAX function

[derochema]

I am trying to select many rows, but only the MAX rows of each
distinct lead_seq. I don't want to actually select the MAX rows, just
make it a condition.

This is what I have:

declare @in_report_date datetime
SET @in_report_date = '07/06/2003'
select lah.lead_seq
FROM lead_action_history lah
RIGHT outer join lead_master lm on lm.lead_seq = lah.lead_seq
WHERE lah.lead_action_date = (select max(lah.lead_action_date) from
lead_action_history)
GROUP BY lah.lead_seq, lah.lead_action_date

I've tried implementing a HAVING with no luck. Is there a simple
solution?
Any ideas would be much appreciated.

Thanks!

Mike

[Anith Sen]

Seems like you posted only part of the code, where is the use of the locally
declared variable? Also there is no correlation in your subquery. Based on
some guesses, here is a try:

SELECT lah.lead_seq,....
FROM lead_action_history lah
RIGHT OUTER JOIN lead_master lm
ON lm.lead_seq = lah.lead_seq
AND lah.lead_action_date = (SELECT MAX(lah1.lead_action_date)
FROM lead_action_history lah1
WHERE lah1.lead_seq = lah.lead_seq)
GROUP BY lah.lead_seq, lah.lead_action_date ;

If this is not what you are looking for, please post your table structures &
sample data along with expected results so that others can understand your
requirements better.

--
- Anith
( Please reply to newsgroups only )