unique grouping with more than one column results

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)

unique grouping with more than one column results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-18 : 09:15:54

robert writes "Here is my table

form_id type1 type2 qty
1 10 S 5
1 10 S 1
1 10 S 1
2 29 M 1
2 29 M 6
3 10 S 2
3 10 S 4
4 12 M 1
4 12 M 0
5 29 M 2
6 10 S 3

and I looking for a select statement that will give me the highest qty within each form ID along with type1,type2 and qty
the result should look like:

form_id type1 type2 qty
1 10 S 5
2 29 M 6
3 10 S 4
4 12 M 1
5 29 M 2
6 10 S 3

Thanks

Rob"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-18 : 09:16:42

SELECT form_id, type1, type2, MAX(qty) AS qty
FROM myTable
GROUP BY form_id, type1, type2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources