CODEWITHSUNDEEP
sqlsql-serverselecttop-n
JohnSmith82
JohnSmith82

Reputation: 55

Select minimal values from two different columns

I have to do a SQL statement to select for each day the min value from a column and when the value Order is the same the Max value from percentage.

Example: 

Date           Order            Percentage           
-------------------------------------------
01-03-2016      1                   0 
01-03-2016      2                   20
02-03-2016      1                   0
02-03-2016      2                   20
03-03-2016      2                   50
03-03-2016      2                   20

The result that I want is something like:

Date           Order            Percentage           
-------------------------------------------
01-03-2016      1                   0 
02-03-2016      1                   0
03-03-2016      2                   50

Upvotes: 1

Views: 78

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220952

If you're using SQL Server 2012+, you could use the following solution:

SELECT DISTINCT
  [Date],
  FIRST_VALUE ([Order])   OVER (PARTITION BY [Date] ORDER BY [Order] ASC, [Percent] DESC),
  FIRST_VALUE ([Percent]) OVER (PARTITION BY [Date] ORDER BY [Order] ASC, [Percent] DESC)
FROM (
  VALUES('2016-03-01', 1, 0),
        ('2016-03-01', 2, 20),
        ('2016-03-02', 1, 0),
        ('2016-03-02', 2, 20),
        ('2016-03-03', 2, 50),
        ('2016-03-03', 2, 20)
) AS t([Date], [Order], [Percent])

How does it work? For each partition (i.e. "group") we're selecting the first value, ordered by [Order]. If two first values for [Order] are the same, then order by [Percent] descendingly. I.e. pretty much the requirement from your question.

Because the first value is the same for the entire partition, we can use DISTINCT to remove duplicates, afterwards.

A note on performance:

Be cautious with this solution, especially on SQL Server. A ROW_NUMBER() based solution as suggested here will outperform mine, slightly on Oracle, and drastically on SQL Server (see comments)

Upvotes: 3

Mureinik
Mureinik

Reputation: 311508

You could use row_number to sort the rows within each such group, and take the first one per group:

SELECT [Date], [Order], [Percentage]
FROM   (SELECT [Date], [Order], [Percentage],
               ROW_NUMBER() OVER (PARTITION BY [Date]
                                  ORDER BY [Order] ASC, [Percentage] DESC) AS rk
        FROM   mytable) t
WHERE  rk = 1

Upvotes: 4

Related Questions