MS SQL

MS SQL RSS

max value

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2006

Posts: 62

Reputation: Fenerbahce is an unknown quantity at this point

Solved Threads: 0

Fenerbahce Fenerbahce is offline

Offline

Junior Poster in Training

max value

Jan 11th, 2007

hi,

table : kisiler

name surname points
----- -------- ----
ali elli 1
ali elli 5
ali elli 5
veli kollu 3
veli kollu 4
adem bacakli 2
ali elli 4

how can i get reqult which has been shown below ? MAX value

ali elli 5
ali elli 5

thanks

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: max value

Jan 11th, 2007

not 100% sure what you are going for, but hopefully one of these will either do it for you or get you going in the right direction

1. this will give you each person and their max

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT name,surname,max(points)AS mp FROM kisiler GROUP BY name,surname
select name,surname,max(points)as mp from kisiler group by name,surname

2. this will give you each person and all instances of their max

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT k.* FROM kisiler k
INNER JOIN (SELECT name,surname,max(points)AS mp FROM kisiler GROUP BY name,surname) c
ON c.name = k.name AND c.surname = k.surname AND k.points = c.mp
select k.* from kisiler k
inner join (select name,surname,max(points)as mp from kisiler group by name,surname) c
on c.name = k.name and c.surname = k.surname and k.points = c.mp

3. this will give you all instance of anyone who got the max points

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT * FROM kisiler  WHERE points = (SELECT max(points) FROM kisiler)
select * from kisiler  where points = (select max(points) from kisiler)