Member Avatar for Fenerbahce

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

  • 2 Contributors
  • 3 Replies
  • 119 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by campkev

Dani AI

Generated

Good thread — covered the simple GROUP/JOIN approaches. Two other concise options that are often more convenient and performant are window functions (available in SQL Server 2005+) and a correlated subquery.

Using a window function keeps every original row and returns all ties for the per-person maximum:

SELECT name, surname, points
FROM (
  SELECT *,
         DENSE_RANK() OVER (PARTITION BY name, surname ORDER BY points DESC) AS rnk
  FROM kisiler
) t
WHERE rnk = 1;

If you want exactly one row per person even when multiple rows tie, replace DENSE_RANK() with ROW_NUMBER() (it arbitrarily picks one top row per partition).

A simple correlated-subquery alternative (also easy to read) returns every row whose points equals that person’s max:

SELECT k.*
FROM kisiler k
WHERE k.points = (
  SELECT MAX(k2.points)
  FROM kisiler k2
  WHERE k2.name = k.name
    AND k2.surname = k.surname
);

Performance notes: for large tables prefer the window version or ensure a composite index on (name, surname, points) so the engine can compute maxima efficiently. If points can be NULL, MAX ignores NULLs and window ordering will treat NULLs as lowest values; plan accordingly. For reference on the functions used see DENSE_RANK (Transact-SQL) and ROW_NUMBER (Transact-SQL).

Recommended Answers

All 3 Replies

Member Avatar for campkev

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

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

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

select * from kisiler  where points = (select max(points) from kisiler)

if you need more help let me know

Member Avatar for Fenerbahce

Thank you very very very much,

You realy give very important eamples. they will help me future.

Thank again.

Member Avatar for campkev

you're quite welcome

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.