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_his tory lah
RIGHT outer join lead_master lm on lm.lead_seq = lah.lead_seq
WHERE lah.lead_action _date = (select max(lah.lead_ac tion_date) from
lead_action_his tory)
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]

Re: SQL question using MAX function

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_his tory lah
RIGHT OUTER JOIN lead_master lm
ON lm.lead_seq = lah.lead_seq
AND lah.lead_action _date = (SELECT MAX(lah1.lead_a ction_date)
FROM lead_action_his tory 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 )