CODEWITHSUNDEEP
mysql
DauleDK
DauleDK

Reputation: 3423

Select entire row based on max value with where clause

In MySQL I'm tasked with the following accounting task:

I took a snippet from the database to demonstrate my problem.

input:

date        text
----------  ----
2015-05-01   b
2015-04-30   a
2015-04-29   b
2015-04-29   a
2015-04-28   b

expected:

date        text
----------  ----
2015-04-29   b

I have tried the following: 

SELECT MAX(date), text
FROM table1
WHERE MONTH(date) = 4
AND text = b

But it does not return the correct text. I have tried solving the problem using this link SQL Select only rows with Max Value on a Column - but I can't get it to work.

What to do?

Upvotes: 2

Views: 112

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You can ORDER BY the date column in descending order and then retain only the first row, which is the row with the largest (i.e. most recent) date:

SELECT *
FROM table1
WHERE text = 'b' AND MONTH(date) = 4
ORDER BY date DESC
LIMIT 1

Upvotes: 2

Related Questions