Ms Sql Server Select Rows With Max Values

Good day I have Table1: COLUMN1 COLUMN2 Column3 ---------------------------- Eva Apple 1 Eva Apple 2 Eva Apple 3 Eva

Solution 1:

You can use row_number

Select top (1) with ties *from table1 
   orderbyrow_number() over (partitionby Column1, Column2 orderby Column3 desc)

Other way is to use outer query:

Select*from (
   Select*, RowN =row_number() over (partitionby Column1, Column2 orderby Column3 desc) from table1 ) a
   Where a.RowN =1

Solution 2:

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
GROUPBY Column1, Column2

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

Solution 3:

select*from (
SELECT*, rn=ROW_NUMBER() over (partitionby COLUMN1,COLUMN2,Column3 orderby 
Column3  desc)
FROM Table1
)
WHERE rn=1

Solution 4:

Please try the following:

WITH B AS
(
    SELECT
        Column1, Column2, Column3,
        ROW_NUMBER() OVER (PARTITIONBY Column1, Column2 ORDERBY Column3 DESC) AS row_num
    FROM
        Table1
)
SELECT Column1, Column2, Column3
FROM B
WHERE row_num =1

Solution 5:

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
GROUPBY Column3  ;