How to select data rows from sql server with the maximum value?

Question

I have a sql server table named Student like below:

I wish to select the students with the highest score from each class, which shall produce the output like this:

Due to some constraint, I can't be sure how many unique class names would exist in the table. My stored procedure is :

  create procedure selectBestStudent
  as
  begin
  select Name, max(TestScore)
  from [TestDB1].[dbo].[StudentTest]
  group by Name
  end

But the result is wrong. Any idea?

see: dba.stackexchange.com/questions/1002/how-to-get-the-max-row — Andrew Bickerton
– Andrew Bickerton, Commented Mar 2, 2016 at 15:14
Possible duplicate of Grabbing the row with the highest value from each group? — Tab Alleman
– Tab Alleman, Commented Mar 2, 2016 at 15:38

Giorgos Betsos · Accepted Answer · 2016-03-02 15:13:08Z

2

You can use ROW_NUMBER with a PARTITION BY:

SELECT Name, Class, TestScore
FROM (
  SELECT Name, Class, TestScore,
         ROW_NUMBER() OVER (PARTITION BY Class 
                            ORDER BY TestScore DESC) AS rn
  FROM StudentTest) AS t
WHERE t.rn = 1

ROW_NUMBER enumerates records within each Class partition: the ORDER BY clause guarantees that the record having the greatest TestScore value is assigned a value equal to 1.

Note: To handle ties you can use RANK in place of ROW_NUMBER. This way you can get all students that share the same maximum TestScore for the same Class.

answered Mar 2, 2016 at 15:13

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Giorgos Betsos Over a year ago

You can try adding a COUNT(*) OVER (PARTITION BY Class)

Mary June Over a year ago

Thank you :) I really appreciate your reply :)

user5992977 Over a year ago

I hate them.. take your +1 back:)

user5992977 · Accepted Answer · 2016-03-02 15:16:43Z

1

You can also achieve this goal with NOT EXISTS()

SELECT * FROM Student s
WHERE NOT EXISTS(select 1 FROM Student t
                 where t.class = s.class
                      and t.testScore > s.testScore)

This will select only those rows that doesn't have a row with a higher value on testScore

answered Mar 2, 2016 at 15:16

user5992977

Comments

if-trubite · Accepted Answer · 2016-03-02 16:36:34Z

I think you will have a problem with the Group By and the MAX() when there are multiple people with the same score in a class.

I solved it with a fetch if you don't know yet what this is, you can look here. It's easier than it looks at the beginning!

I know that might be a horrible way to do it but its's easy to understand and it worked! :D

USE [TestDB]
GO

DECLARE @class char(10), @testscore int;
DECLARE @result Table
(
    Name char(10),
    Class  char(10),
    TestScore int
);
-- Get Classes and their Maxima 
DECLARE TestScore_cursor CURSOR FOR SELECT [class], MAX([testscore]) FROM [student] GROUP BY [class];

OPEN TestScore_cursor;

-- Perform the first fetch.
FETCH NEXT FROM TestScore_cursor INTO @class, @testscore;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Search Students by Class and Score and add them to tempTable @result
   INSERT INTO @result SELECT [name], [class], [testscore] From [student] where [testScore] = @testscore AND [class] = @class;

   FETCH NEXT FROM TestScore_cursor INTO @class, @testscore;
END

-- Show the Result
SELECT * FROM @result;

CLOSE TestScore_cursor;
DEALLOCATE TestScore_cursor;
GO

Collectives™ on Stack Overflow

How to select data rows from sql server with the maximum value?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related