CODEWITHSUNDEEP
sql-serverdatabase
Mary June
Mary June

Reputation: 159

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

I have a sql server table named Student like below:

enter image description here

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

enter image description here

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?

Upvotes: 0

Views: 56

Answers (3)

if-trubite
if-trubite

Reputation: 159

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

Upvotes: 1

user5992977
user5992977

Reputation:

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

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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.

Upvotes: 2

Related Questions