Monday, February 20, 2012

Max() and Query...

Hi i got the following problem... i got a Table with multiple Scores for
People.... Something Like This
ID NAME POINTS
1 JOE 15
2 JOE 20
3 CHRIS 19
4 MATT 40
5 CHRIS 1
6 MATT 30
i need a query that will return the max of points that a player did along
with the name of the player and the id, if i didn't have the id i just
could group by, but the id is messing my group by, and returning me
everything... how can i do it '
Thanks.
AlexAlejandro K. wrote:
> Hi i got the following problem... i got a Table with multiple Scores
> for People.... Something Like This
> ID NAME POINTS
> 1 JOE 15
> 2 JOE 20
> 3 CHRIS 19
> 4 MATT 40
> 5 CHRIS 1
> 6 MATT 30
> i need a query that will return the max of points that a player did
> along with the name of the player and the id, if i didn't have the id
> i just could group by, but the id is messing my group by, and
> returning me everything... how can i do it '
> Thanks.
> Alex
Select ID,Name,maxpoints
FROM table t inner join (
SELECT Name,Max(Points) maxpoints
FROM table
GROUP BY Name) q
on t.Name=q.Name and t.points = q.maxpoints
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Try using a derived table:
create table t1 ([ID] int identity, [NAME] varchar(16), POINTS int)
insert into t1 ([NAME], POINTS) values ('JOE', 15)
insert into t1 ([NAME], POINTS) values ('JOE', 20)
insert into t1 ([NAME], POINTS) values ('CHRIS', 19)
insert into t1 ([NAME], POINTS) values ('MATT', 40)
insert into t1 ([NAME], POINTS) values ('CHRIS', 1)
insert into t1 ([NAME], POINTS) values ('MATT', 30)
select a.[ID],
b.[NAME],
b.[POINTS]
from t1 a,
(select [NAME],
MAX(POINTS) as POINTS
from t1
group by [NAME]) b
where a.[NAME] = b.[NAME]
and a.POINTS = b.POINTS
-- Jesse
"Alejandro K." <AleK@.AleK.com> wrote in message
news:Xns95F8A6D6A6BD2AleKAleKcom@.207.46.248.16...
> Hi i got the following problem... i got a Table with multiple Scores for
> People.... Something Like This
> ID NAME POINTS
> 1 JOE 15
> 2 JOE 20
> 3 CHRIS 19
> 4 MATT 40
> 5 CHRIS 1
> 6 MATT 30
> i need a query that will return the max of points that a player did along
> with the name of the player and the id, if i didn't have the id i just
> could group by, but the id is messing my group by, and returning me
> everything... how can i do it '
> Thanks.
> Alex

No comments:

Post a Comment