problem with order by and group by

[skoff]

Hi guys!
I've been trying for hours to make this work but i can't...

Database info :
table : standing
columns name : conference,division,team,points

So what i've been trying to do is to do a standing system just like in the NHL. So what i need to do is to find the best team of each division.

I tried this first query :
"SELECT conference,division,team,points FROM standing GROUP BY division order by points desc"

and it gave me this result :
Conf-¦-Division-¦----Team----¦PTS-¦
-----¦----------¦------------¦----¦
East-¦Atlantic--¦-New Jersey-¦ 62 ¦
West-¦South-West¦--Anaheim---¦ 56 ¦
East-¦North-East¦---Boston---¦ 54 ¦
West-¦Central---¦--Chicago---¦ 53 ¦
West-¦North-West¦--Calgary---¦ 51 ¦
East-¦South-East¦-Atlanta----¦ 50 ¦

the problem is that this result isnt what i ask.. it just gave me the first team in alphabetic order and give me the points that they have but these team arent the best team of their division. So i tried this query as a test only with one division :
"SELECT team,max(points) from standing where division='Atlantic' group by division"

and it gave me this result :
Conf--¦Division--¦---Team---¦-PTS-¦
------¦----------¦----------¦-----¦
East--¦Atlantic--¦New Jersey¦-68--¦

in this result the points are good but the team doesnt fit with the points... the team with 68 points is Philadelphia and not New Jersey.. So if someone could help me to work this out it would really help me! thanks for your time!

[gopika gokul]

Hi Skoff,
Instead of "SELECT conference,division,team,points FROM standing GROUP BY division order by points desc" ,
you can try
"SELECT conference,division,team,points FROM standing ORDER BY division, points DESC

[skoff]

yeah this is working but the thing is that I only need the first team of each division. When i say the first team it's the team that got the most points. This is why i added the "group by" function... But its just mixing up everything.. :S

[skoff]

someone else have any other idea? thanks for your help gopika if you have any other idea tell me! thanks :D :D

I tried this query :
select team from standing join (select division,max(points) as mx_points from standing group by division) as mx_standing on standing.division=mx_standing and standing.points=standing.mx_points

but I get this error :
Unknown column 'mx_standing' in 'on clause'

[skoff]

please i really need help with this