MS SQL Server select rows with max values

Question

Good day

I have Table1:

COLUMN1    COLUMN2   Column3  
----------------------------
Eva           Apple       1
Eva           Apple       2
Eva           Apple       3
Eva           Apple       4
Eva           Apple       5
Eva           Apple       6
Bob           Apple       1
Bob           Samsung     1
Bob           Samsung     2
...           ...        ...

I need

COLUMN1    COLUMN2   Column3
----------------------------
Eva           Apple       6
Bob           Samsung     2
Bob           Apple       1
...           ...        ...

How i can setup string for select only rows with MAX values in Column3 ?

My version of string is :

SELECT MAX(Column3) , [column2], [Column2] 
FROM Table1
WHERE Column3 =  MAX ;

Thanks for Opinions

Possible duplicate of [Select max value of each group](https://stackoverflow.com/questions/4510185/select-max-value-of-each-group) — Tab Alleman, Aug 03 '17 at 13:15

score 2 · Answer 1 · answered Aug 03 '17 at 12:57

2

You can use row_number

Select top (1) with ties *
   from table1 
   order by row_number() over (partition by Column1, Column2 order by Column3 desc)

Other way is to use outer query:

Select * from (
   Select *, RowN = row_number() over (partition by Column1, Column2 order by Column3 desc) from table1 ) a
   Where a.RowN = 1

answered Aug 03 '17 at 12:57

Kannan Kandasamy

13,405
3
25
38

Thanks, this was very helful! – Gazeciarz Jul 01 '21 at 23:27

score 1 · Answer 2 · answered Aug 03 '17 at 12:58

1

select * from (
SELECT *, rn=ROW_NUMBER() over (partition by COLUMN1,COLUMN2,Column3 order by 
Column3  desc)
FROM Table1
)
WHERE rn=1

answered Aug 03 '17 at 12:58

Gagan Sharma

220
1
7

1

Very low effort answer. Please add some explanation to your answer. – Timmetje Aug 03 '17 at 13:17
Partition by will divide the results of table as the Column3 order is by desc order you will get the max result , that is why i have mentioned rn=1. Try Practically its very fast and easily. – Gagan Sharma Aug 03 '17 at 13:21
1

You should add it all in the actual answer, not comments. I'm a reviewer, I'm not asking explanation for myself ;) – Timmetje Aug 03 '17 at 13:24
1

I know, but when the user execute the code the result will be correct. Next time i will surely add descriptions on my any answer :) – Gagan Sharma Aug 04 '17 at 04:53

score 1 · Answer 3 · answered Aug 03 '17 at 13:01

Please try the following:

WITH B AS
(
    SELECT
        Column1, Column2, Column3,
        ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY Column3 DESC) AS row_num
    FROM
        Table1
)
SELECT Column1, Column2, Column3
FROM B
WHERE row_num = 1

score 1 · Accepted Answer · answered Aug 03 '17 at 13:01

1

You want to find the maximum Column3 for each combination of Column1 and Column2.

You can achieve this with a GROUP BY

SELECT Column1, Column2, MAX(Column3)
FROM Table1
GROUP BY Column1, Column2

See https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

answered Aug 03 '17 at 13:01

FJT

1,923
1
15
14

its problém wit numbers over 99 and 999 – Luka Aug 03 '17 at 13:25
1

Are your numbers stored as numbers, or as text? What sort of problem are you having? – FJT Aug 03 '17 at 15:57

score 0 · Answer 5 · answered Aug 03 '17 at 12:56

You need to add a group by. In queries of this type, you have a set of columns you want the values from (these are the columns you group by) and you have other columns most of whose values you'll throw away. You use a function like MAX, MIN, SUM, AVG to specify what to do with the data from rows that are "thrown away". The result is a unique set of values from the columns that were grouped, and a single value corresponding to the min/max/avg etc from the columns that were not grouped:

SELECT [column1], [Column2], MAX(Column3) as Column3
FROM Table1
GROUP BY Column3  ;

score 0 · Answer 6 · answered Aug 03 '17 at 12:58

0

Add a Group by on column 3.

Select col1, col2, max(col3)
from test
Group By col3

answered Aug 03 '17 at 12:58

Ragha Raj

98
4

I thought the column 3 was of integer data type – Ragha Raj Aug 04 '17 at 10:59

score 0 · Answer 7 · answered Aug 03 '17 at 13:08

You can use a function like DENSE_RANK().

In this example, if there are duplicate values you want to retrieve.

declare @t as table (COLUMN1   char(3),  COLUMN2 varchar(10),   COLUMN3  int)



INSERT @t (COLUMN1    , COLUMN2   , COLUMN3  ) select 'Eva',           'Apple'       ,1
INSERT @t (COLUMN1    , COLUMN2   , COLUMN3  ) select 'Eva',           'Apple'       ,2
INSERT @t (COLUMN1    , COLUMN2   , COLUMN3  ) select 'Eva',           'Apple'       ,3
INSERT @t (COLUMN1    , COLUMN2   , COLUMN3  ) select 'Bob',           'Apple'       ,1
INSERT @t (COLUMN1    , COLUMN2   , COLUMN3  ) select 'Bob',           'Samsung'     ,1
INSERT @t (COLUMN1    , COLUMN2   , COLUMN3  ) select 'Bob',           'Samsung'     ,2


SELECT * FROM (
SELECT DENSE_RANK() OVER (PARTITION BY COLUMN1, COLUMN2 ORDER BY COLUMN1, COLUMN3 desc)  [Max] , * from @t ) as T
WHERE max = 1 -- Set here what position do you want 
Order by COLUMN3

MS SQL Server select rows with max values

7 Answers7

Related