CODEWITHSUNDEEP
mysql
Nebu
Nebu

Reputation: 1793

Most efficient way to get min and max value with date column

I want to get the min and max value for a column with the associated date column included. Below a simple table example and the desired output.

ID  SCORE   DATE
1   100     1-1-2018
2   99      2-1-2018
3   102     3-1-2018
4   99      4-1-2018
5   98      5-1-2018
6   102     6-1-2018
7   100     7-1-2018

Output should be

MinValue    MinDate     MaxValue    MaxDate
98          5-1-2018    102         6-1-2018

If a min or max value occurs multiple time the last date should be returned.

By using an inner join i can get the appropriate date for a min OR max value in one query but not both. See example below:

SELECT score as MinValue, date as MinDate 
FROM table inner join (select min(score) from table ) x on table.score= x.score
ORDER BY date desc
LIMIT 1

Here is a sqlfiddle http://sqlfiddle.com/#!9/f50ced/4

Is it possible and efficient to extract all required values in one query?

Upvotes: 3

Views: 3636

Answers (2)

cn0047
cn0047

Reputation: 17071

You may try this:

select
  @minv := (select min(SCORE) from tbl) as MinValue,
  (select min(DATE) from tbl WHERE SCORE = @minv) as MinDate,
  @maxv := (select max(SCORE) from tbl) as MxValue,
  (select max(DATE) from tbl WHERE SCORE = @maxv) as MaxDate
;

Result looks like this:

+----------+----------+---------+----------+
| MinValue | MinDate  | MxValue | MaxDate  |
+----------+----------+---------+----------+
|       98 | 5-1-2018 |     102 | 6-1-2018 |
+----------+----------+---------+----------+

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Here is one approach; not sure if it is the fastest.

  • Fetch Max and Min value in one Derived Table.
  • "Self" join this table twice to get corresponding date(s) for minimum and maximum values.
  • Interestingly, MaxValue is a Reserved Keyword in MySQL; so had to use backticks (`) around it.

Try:

SELECT MIN(t1.minValue) AS minValue, 
       MAX(t2.Date) AS minDate, 
       MAX(t1.`maxValue`) AS `maxValue`, 
       MAX(t3.Date) AS maxDate 
FROM
    (
     SELECT MIN(score) AS minValue, 
            MAX(score) AS `maxValue` 
     FROM your_table
    ) AS t1 
    JOIN your_table AS t2 ON t2.score = t1.minValue 
    JOIN your_table AS t3 ON t3.score = t1.`maxValue`

Upvotes: 1

Related Questions