question

VolginRnB avatar image
0 Votes"
VolginRnB asked LukasYu-msft edited

SQL Server Query

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

84622-image.png


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!


84572-image.png


sql-server-generalsql-server-transact-sqlsql-server-analysis-services
image.png (17.6 KiB)
image.png (7.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft edited

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



· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lukas, for Class = 2, you query will return David and 100. But there is no such row in the data. Look at Viorel's post for how to do it.

0 Votes 0 ·

Yes, indeed, my short answer was not thought through. It was wrong. I would change it. Thanks for the correction :)

0 Votes 0 ·

I I was needing exactly this, thanks @LukasYu-msft

0 Votes 0 ·

Hi,

The old post as Erland pointed out would make up non-existing records. It is not a proper solution.

I modified Viroel's code to suit your need. Credit to Viorel :)

Cheers.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VolginRnB avatar image
0 Votes"
VolginRnB answered Viorel-1 commented

@Viorel-1 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

84588-image.png


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

84635-image.png


What SQL Server Query Should I Use for it ?


image.png (18.9 KiB)
image.png (5.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Why Anthony is better than Andrew?

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.