How to get date before last date in mysql-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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;