Second latest date in MySQL

Databases
1

Hi Expert,

In MySQL, how can I get second latest date? I have the following table and data:

TABLE 1 and 2:

Table1:
ID, DNUMBER, FDATE
1, 1234, 2014-01-01
2, 1235, 2014-01-01
3, 1234, 2014-01-02
4, 1234, 2014-01-03
5, 1234, 2014-01-04

Table2:
ID, DNUMBER
1, 1234
2, 1235

My EXPECTED RESULT:

ID, table2.DNUMBER, table1.DNUMBER, LATESTDATE, 2ndLATESTDATE
1, 1234, 1234, 2014-01-04, 2014-01-03
2, 1235, 1235, 2014-01-01, NULL

I managed to get most latest date, with MySQL Max() function.

Question: How about second latest date?


SELECT
table2.ID,
table2.DNUMBER,
table1.DNUMBER,
MAX(table1.FDATE) AS LATESTDATE
FROM table2
LEFT OUTER JOIN table1 ON table2.DNUMBER = table1.DNUMBER
GROUP BY table2.DNUMBER
2 
SELECT
table2.ID,
table2.DNUMBER,
table1.DNUMBER,
MAX(table1.FDATE) AS LATESTDATE
FROM table2
LEFT OUTER JOIN table1 ON table2.DNUMBER = table1.DNUMBER
[B]WHERE table1.FDATE < (SELECT MAX(table1.FDATE) FROM table1)[/B]
GROUP BY table2.DNUMBER

But this query is quite inefficient (max is an expensive locking function). If you need to use it a lot, you should definately ensure there is an index on table1.FDATE, and even better, consider a more suitable table architecture.

3

Thanks, but the SQL codes do not always return desired/correct result.
Is it because of the date cannot be used with comparison of > < etc?

But, the Max function of MySQL works perfectly.

SELECT
table2.DNUMBER,
table1.DNUMBER,
table1.DRAWDATE
FROM table2
LEFT OUTER JOIN table1 ON table2.DNUMBER = table1.DNUMBER
WHERE (table1.DRAWDATE < (SELECT MAX(table1.DRAWDATE) FROM table1))
GROUP BY table2.DNUMBER
4

Try this query:


select    t1.dnumber,
              max(first_date),
              max(t2.fdate) second_date
from
(
             select  dnumber,
                          max(fdate) first_date
             from    test
             group by dnumber
) t1
LEFT outer join
test t2
on  (t1.dnumber = t2.dnumber and t2.fdate < t1.first_date)
group by t1.dnumber
5

Woops I didn’t read your question properly, you want the second latest date AND the latest date.

This should work:

SELECT
table2.ID,
table2.DNUMBER,
table1.DNUMBER,
MAX(table1.FDATE) AS LATESTDATE,
(SELECT MAX(table1.FDATE) FROM table1 WHERE table1.FDATE < (SELECT MAX(table1.FDATE) FROM table1)) AS secondLatestDate
FROM table2
LEFT OUTER JOIN table1 ON table2.DNUMBER = table1.DNUMBER
GROUP BY table2.DNUMBER
6

Thanks RT,

I tried your code … and the code of

(SELECT MAX(table1.FDATE) FROM table1 WHERE table1.FDATE < (SELECT MAX(table1.FDATE) FROM table1)) AS secondLatestDate

Actually did return the expected result of secondlates date, but it does not work with the LEFT OUTER JOIN correctly, in which the result return …

ID, table2.DNUMBER, table1.DNUMBER, LATESTDATE, 2ndLATESTDATE
1, 1234, 1234, 2014-01-04, 2014-01-02
2, 1235, 1235, 2014-01-01, 2014-01-02

This seems that the code return result correctly, but does not “pair” with the JOINT table.

Note: I am still try to figure out suggestion by vpalukuru9.

7

Ah, because you want the second latest time which is in the rows of table 1 that are joined to table 2. Rather than just table 1.

SELECT
table2.ID,
table2.DNUMBER,
table1.DNUMBER,
MAX(table1.FDATE) AS LATESTDATE,
(
    SELECT MAX(table1.FDATE) 
    FROM table1 
    WHERE table2.DNUMBER = table1.DNUMBER 
    AND table1.FDATE < (
        SELECT MAX(table1.FDATE) 
        FROM table1 
        WHERE table2.DNUMBER = table1.DNUMBER
    )
) AS SECONDLATESTDATE
FROM table2
LEFT OUTER JOIN table1 ON table2.DNUMBER = table1.DNUMBER
GROUP BY table2.DNUMBER
8

…
Thanks a lot RT.

The code works perfectly :slight_smile:

9

UPDATE tbl SET datetime = DATE(date) + INTERVAL 1 DAY - INTERVAL 1 SECOND