Help with max()

I have two tables:
TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id.
TABLE2 has policy_id
I'm doing a join, WHERE table1.policy_id = table2.policy_id.
I need to display the value of many fields from both tables where
table1.subm_no is the max value of that subm_no FOR THAT POLICY_ID. Can
someone point me in the right direction? Thanks.SELECT Table1.*, Table2.*
FROM Table1
JOIN Table2 ON Table1.policy_id = Table2.policy_id
WHERE Table1.Subm_No =
(
SELECT MAX(Subm_No)
FROM Table1 Tx
WHERE Tx.policy_id = Table1.policy_id
)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1d98a20592f0f5359898f9@.msnews.microsoft.com...
> I have two tables:
> TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id.
> TABLE2 has policy_id
> I'm doing a join, WHERE table1.policy_id = table2.policy_id.
> I need to display the value of many fields from both tables where
> table1.subm_no is the max value of that subm_no FOR THAT POLICY_ID. Can
> someone point me in the right direction? Thanks.