'How to get date before last date in mysql

How to get date before last date in mysql if i have table below

Date    customerID
04/01/2016  3
04/01/2016  3
08/01/2016  3
08/01/2016  3
11/01/2016  3

for this customerID the output would be

last Date 11/01/2016
date before last 08/01/2016
mysqlsqldatetimeonbeforeunload


Solution 1:[1]

if you want to select the second last row in your record you can use the LIMIT function in mysql. You can read more about limit here.

I suggest to try this solution:

SELECT * FROM your_table ORDER BY date DESC LIMIT 1, 1

And here another link for the example here

Hope it helps.

Solution 2:[2]

On MySQL 8+, ROW_NUMBER works nicely here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Date DESC) rn
    FROM yourTable
    WHERE CustomerID = 3
)

SELECT 'last date' AS label, Date FROM cte WHERE rn = 1
UNION ALL
SELECT 'date before last', Date FROM cte WHERE rn = 2
ORDER BY Date DESC;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 fonz
Solution 2 Tim Biegeleisen

Related Questions