Inner JOIN - MAX Value - TSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inner JOIN - MAX Value - TSQL

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2006-06-19 : 20:49:22

I have 2 tables joined by CID value

table 1

pid cid
1 a1
2 a2
3 a3

table 2

pid cid cNo
1 a1 20060
2 a1 20061
3 a1 20063
4 a2 50800
5 a2 50801
6 a3 50900

I want the following output (Inner join by cid and display only the highest CNO for each cid)

a1 20063
a2 50801
a3 50900

Thanks
Cindy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-19 : 21:13:15

[code]select t2.cid, max(t2.cNo)
from table1 t1 inner join table2 t2
on t1.cid = t2.cid
group by t2.cid[/code]

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-19 : 23:24:57

SELECT
a.cid, MAX(a.cNo)
FROM
tbl1 b
INNER JOIN tbl2 a
ON b.cid = b.cid

GROUP BY b.cid
-- KK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources