Aggregrate Function - Max() - 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

General SQL Server Forums

New to SQL Server Programming

Aggregrate Function - Max()

Author

Topic

MacGyver
Starting Member

1 Post

Posted - 2006-08-16 : 11:08:22

Hi,

This is the querey I have put in:

SQL> select p.playername, count(g.goalscorerID) as GOALS
2 from tvs.players p, tvs.goals g
3 where p.playerID = g.goalscorerID
4 group by p.playername;

Output :

PLAYERNAME GOALS
------------------------------ ---------------
Thierry Henry 4
Lionel Messie 1
Samuel Eto 5
Zidane 1
Ronaldhino 2
Ronaldo 5

And now what I want to do is use the MAX() function to list the top scorers, so it would look like this.

PLAYERNAME GOALS
------------------------------ ---------------
Ronaldhino 5
Ronaldo 5

I am having trouble doing this, this is one of the queries I have tried .

SQL> select c.customername, count(v.soldto) as Borrowcount
2 from tvs.customer c, tvs.video v
3 where c.customerid = v.soldto
4 group by c.customername
5 having max(Borrowcount);

So does anyone know how to do this?

Thanks in advance for any input.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 11:12:45

select customername, max(Borrowcount) as MaxBorrowcount
from (your group by query here) t
group by customername
order by 2

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources