Easier way to select MAX() based on conditions?

Question

Hi,

Given the following table format...

id | date       | value
___|____________|______
11 | 2010-01-01 | 50
11 | 2010-01-02 | 100
12 | 2010-01-01 | 150
12 | 2010-01-02 | 200

... I need to select the id that corresponds to the maximum value on a day that I specify. The only way I've figured out how to do this so far is using a sub-query as follows:

SELECT id
    FROM table
    WHERE date =  '2010-01-01'
        AND value = ( 
            SELECT MAX(value)
                FROM table
                WHERE date = '2010-01-01'
                GROUP BY date
        )

On a table with ~70,000 records, with a primary key over id and date, this takes ~0.25 seconds to execute, which seems a long time to me. Is there a faster way for me to achieve the same result?

Thanks.

Answer 1

MySQL had grown pretty lenient on what you get back from a GROUP BY query - meaning it doesn't have to be just aggregate or GROUP BY columns. You should be fine getting the PRIMARY KEY See what the following gives you.

SELECT id, MAX(value) FROM table WHERE date = '2010-01-01' GROUP BY date;

Answer 2

SELECT TOP 1 id FROM table WHERE date = '2010-01-01' ORDER BY value DESC

Answer 3

tried using a JOIN?

SELECT 
     A.id 
FROM 
     table A
     JOIN (SELECT MAX(value) as m_value
        FROM table 
        WHERE date = '2010-01-01') AS B ON A.value = B.m_value
WHERE 
     A.date =  '2010-01-01'

Answer 4

Collection of Answers

---

Type the date once:

SELECT id
FROM   table
WHERE  (date,value) IN ( select date, max(value) 
                         from   table
                         where  date = '2010-01-01'
                         group by date
                       )

Date twice, no Group By (preferred method):

SELECT id
FROM   table
WHERE  date  = '2010-01-01'
  AND  value = ( select max(value)
                  from   table
                  where  date = '2010-01-01'
                )

Thoughts

---

• The second should be the fastest.
• Any query that performs a join in the FROM statement is equivalent to a subquery in the WHERE clause.
• Any query that uses LIMIT/TOP 1 may not be returning the full resultset, which could negatively impact your application, based on your requirments - you may want all IDs
• Other ways to improve speed:
  1. Create stored procedure
  2. Create an index on (date,value) ID shouldn't need an index in this case