SQL Server Query

Question

I have a database SQL Server and I have a doubt about a query that I need to execute it.

I Have a table and I run a query "select * from mytable" the results is below

I Would like run a query that show only bigger lvl for each class as example below, but I don't have idea how do this query. Anyone can help me ? please!

Answer 1

When multiple persons get same highest lvl, do you have specific rules for picking out which one to be selected? If not, firstly, you could try following see if it fits your need:

  select Name, Class, lvl
     from
     (
         select *, ROW_NUMBER() over (partition by Class order by lvl desc) as r
         from Test1
     ) t
     where r = 1
     order by Class

Answer 2

Check this approach:

select [Name], Class, lvl
from
(
    select *, rank() over (partition by Class order by lvl ) as r
    from MyTable
) t
where r = 1
order by Class desc

Answer 3

@Viorel Sorry! I think that I described wrong, I'm going to explain again.

I Have a table and I run a query "select * from mytable" the results is below

I Would like run a query that show only bigger lvl for each class as example below.

What SQL Server Query Should I Use for it ?

Answer 4

To show all people that have the highest lvl, try this query:

select Name, Class, lvl
from
(
    select *, rank() over (partition by Class order by lvl desc) as r
    from MyTable
) t
where r = 1
order by Class