SQL:How do i get the row that has the max value of a column in SQL Server

Question

I have a data a set which is already grouped by Person and Class columns and I use this query for this process:

SELECT Person,Class, MAX(TimeSpent) as MaxTimeSpent 
FROM Persons 
GROUP BY Person,Class

Output:

Person   Class    MaxTimeSpent 
--------|--------|-------------|
MJ      |   0    | 0           |
MJ      |   1    | 659         |
MJ      |   2    | 515         |

What I want to do is to get the row that has the maximum Class value in this data set (which is the 3rd row for this example).

How can I do this ? Any help would be appreciated.

@StuartLC TOP 1 wont work since i use WHERE caluse to make easier to test my query. — Tartar
– Tartar, Commented Dec 31, 2015 at 9:09
In that case, search for a Group Wise Maximum - a good strategy is to use the partitions to group the data and use ROW_NUMBER (no ties) or RANK (with ties) to number the rows within each group, ordered by Class, and then to filter out just the Row / Rank = 1 rows. — StuartLC
– StuartLC, Commented Dec 31, 2015 at 9:15
See: dba.stackexchange.com/questions/1002/how-to-get-the-max-row — Andrew Bickerton
– Andrew Bickerton, Commented Dec 31, 2015 at 11:00

fabulaspb · Accepted Answer · 2015-12-31 09:16:24Z

2

Try this one

SELECT T.*
FROM
    (SELECT Person,
           Class,
           MAX(TimeSpent) AS MaxTimeSpent 
    FROM Persons AS P
    WHERE Person = 'MJ'
    GROUP BY Person, Class) AS T
WHERE T.class = (
      SELECT MAX(class) FROM Persons AS P
      WHERE P.person = T.person)

edited Dec 31, 2015 at 9:16

answered Dec 31, 2015 at 9:05

fabulaspb

1,2538 silver badges9 bronze badges

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

1 Comment

Andy K Over a year ago

Hi @fabulaspb, yep. nice one

Shiju Shaji · Accepted Answer · 2015-12-31 09:26:43Z

1

You can use cte for that.

 declare @Persons table (person nvarchar(10),Class int ,TimeSpent int)

insert into @Persons
select 'MJ',0,0 union all
select 'MJ',1,659 union all
select 'MJ',2,515 


;with cte
as(
SELECT Person,Class,TimeSpent , row_number() over(partition by Person  order by Class desc )  as RN
FROM @Persons 
)
select * from cte where RN=1

Solution 2 :With Out Cte:

SELECT * FROM (
    SELECT Person
        ,Class
        ,TimeSpent
        ,row_number() OVER (PARTITION BY Person ORDER BY Class DESC) AS RN FROM @Persons
    ) t WHERE t.RN = 1

edited Dec 31, 2015 at 9:26

answered Dec 31, 2015 at 9:09

Shiju Shaji

1,73017 silver badges24 bronze badges

Comments

Shiju Shaji · Accepted Answer · 2015-12-31 09:51:31Z

0

TRY This:

declare @t table (Person  varchar (20),Class    int ,MaxTimeSpent int )
insert into @t VALUES ('MJ',0,0)
insert into @t VALUES ('MJ',1,659)
insert into @t VALUES ('MJ',2,515)

SELECT TOP 1 * FROM @t ORDER BY 2 DESC

--OR
SELECT * FROM @t WHERE Class = (SELECT max(class) FROM @t)

-- OR
SELECT TOP 1 * FROM (
    SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY person ORDER BY Class DESC) Record_Count FROM @t
    ) a

edited Dec 31, 2015 at 9:51

Shiju Shaji

1,73017 silver badges24 bronze badges

answered Dec 31, 2015 at 9:15

MAX

1,5504 gold badges17 silver badges25 bronze badges

Comments

Faizan shaikh · Accepted Answer · 2015-12-31 11:30:43Z

0

SELECT Top 1 Person,Class, MAX(TimeSpent) as MaxTimeSpent FROM Persons GROUP BY Person,Class order by Class desc

answered Dec 31, 2015 at 11:30

Faizan shaikh

13 bronze badges

Collectives™ on Stack Overflow

SQL:How do i get the row that has the max value of a column in SQL Server

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related