CODEWITHSUNDEEP
sqlsql-server
far
far

Reputation: 115

Select rows using group by and in each group get column values based on highest of another column value

I need to get latest field based on another field in group by we have

Table "SchoolReview"

Id SchoolId Review Point
1 1 rv1 8
2 1 rv2 7
3 2 rv3 4
4 2 rv4 7
5 3 rv5 2
6 3 rv6 8

I need to group by SchoolId and the inside group I need to get Review and Point from highest "Id" column.

I dont need "Id" coulmn but even if I get it for this solution its okay.

Result I am looking for shall look like this.

SchoolId Review Point
1 rv2 7
2 rv4 7
3 rv6 8

Any one experienced in MS SQL Server can help in this regard?

Upvotes: 0

Views: 1396

Answers (2)

Geezer
Geezer

Reputation: 497

Using sample data from other answer

SELECT *
INTO #Data
FROM (VALUES
   (1, 1, 'rv1', 8),
   (2, 1, 'rv2', 7),
   (3, 2, 'rv3', 4),
   (4, 2, 'rv4', 7),
   (5, 3, 'rv5', 2),
   (6, 3, 'rv6', 8)
) v (Id, SchoolId, Review, Point)

SELECT S.SchoolId,
       S.Review,
       S.Point 
FROM   #Data S
INNER JOIN
(
    SELECT Id = MAX(S1.Id),
           S1.SchoolId
    FROM   #Data S1
    GROUP BY SchoolId
) X ON X.Id = S.Id AND X.schoolId = S.SchoolId
ORDER BY X.SchoolId    
;

output

enter image description here

Upvotes: 1

Zhorov
Zhorov

Reputation: 30003

You do not need to group the rows, you simply need to select the appropriate rows from the table. In this case, using ROW_NUMBER() is an option:

Table:

SELECT *
INTO Data
FROM (VALUES
   (1, 1, 'rv1', 8),
   (2, 1, 'rv2', 7),
   (3, 2, 'rv3', 4),
   (4, 2, 'rv4', 7),
   (5, 3, 'rv5', 2),
   (6, 3, 'rv6', 8)
) v (Id, SchoolId, Review, Point)

Statement:

SELECT SchoolId, Review, Point
FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY SchoolId ORDER BY Id DESC) AS Rn
   FROM Data
) t
WHERE Rn = 1

Result:

SchoolId Review Point
---------------------
1        rv2    7
2        rv4    7
3        rv6    8

Upvotes: 1

Related Questions