how to select a field from a row that is chosen by group-by in mySQL

Question

I have a GIANT MYSQL database that contains 100,000,000 records.

Simplified, three of my columns are ID, date and time, i have several indexes over id and date ( ID , DATE , ID&DATE ) so no performance problem on join

 select id, max(date) as last_record from mytable where date < "2010-01-02" 

             mytable
 /--------------------------------\
 |    ID  |     date     |  time  |
 |--------+--------------+--------|
 |    1   | 2009-01-01   |   15   |
 |--------+--------------+--------|
 |    1   | 2009-01-03   |   14   | <-- This 
 |--------+--------------+--------|
 |    2   | 2009-01-01   |   18   |
 |--------+--------------+--------|
 |    2   | 2009-01-02   |   12   |
 |--------+--------------+--------|
 |    2   | 2009-01-02   |   15   | <-- and This 
 \--------+--------------+--------/

That results like:

 /-----------------------\
 |    ID  |  last_record |
 |--------+--------------|
 |    1   | 2009-01-03   |
 |--------+--------------|
 |    2   | 2009-01-02   |
 \--------+--------------/

IMPROVED QUESTION: Now i want this query to tell me about the max*time* of the records that are chosen by group by, like:

 /--------------------------------\
 |    ID  |  last_record |  time  |
 |--------+--------------+--------|
 |    1   | 2009-01-03   |   14   |
 |--------+--------------+--------|
 |    2   | 2009-01-02   |   15   |
 \--------+--------------+--------/

I need some idea to do this !

EDIT:(MORE INFORMATION) i want to know last record and the time of that record for all of my Ids

Answer 1

SELECT  *
FROM    mytable
WHERE   date <= '2010-01-02'
ORDER BY
        date DESC
LIMIT 1

If you need the max time per date, use this:

SELECT  m.*
FROM    (
        SELECT  DISTINCT date
        FROM    mytable
        ) md
JOIN    mytable m
ON      id =
        (
        SELECT  id
        FROM    mytable mi
        WHERE   mi.date = md.date
        ORDER BY
                mi.date DESC, mi.time DESC, mi.id DESC
        )

You'll need a composite index on (date, time, id) for this to work fast.

Id should be included even if your table is InnoDB.

See this entry in my blog for more detail:

• MySQL: Selecting records holding group-wise maximum (resolving ties)

Answer 2

I might have missunderstood this .. but it seems pretty simple:

SELECT id, date, time 
FROM table where date < "2010-01-02" 
GROUP BY date
ORDER BY date, time DESC

Answer 3

That would be easier to figure out with your complete SQL statement, but if I get this right, you have a history table:

SELECT id, date, time
FROM table
JOIN tablewithhistory on id = id and date = (SELECT MAX(date) FROM tablewithhistory WHERE date < "2010-01-02" and id = id)

That would give you all the records from table with the max date from your history table.

Answer 4

select id, max(date) as last_record from table where date < "2010-01-02"

This doesn't make a lot of sense

• you are using aggregate functions with non-sgrregated columns but no group by
• conventionally 'id' is used for a unique identifier - so grouping by id would return all rows
• you say that you are concerned with performance - if the date column is a date type then MySQL can't use any indexes to resolve this query.

Assuming that your 'id' column is not unique and a foreign key, then, yes you could do a subselect as suggested by Danny T - but this will result in two passes through the data (and still can't use an index on date) - when you say that performance is important.

To use an index for a date type filter you need to instruct the optimizer to convert the string parameter to a date (using the DATE() function).

The following query will return the results with only a single pass through the dataset:

 SELECT id
     , MAX(`date`)
     , SUBSTR(MAX(CONCAT(DATE_FORMAT(`date`,'%Y%m%d'),time)),9) as lasttime
 FROM `table`
 WHERE `date`>DATE('2010-01-02')
 GROUP BY id;

C.