2

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

  • I have to find the entire row with the newest date in month (given month number)
  • Where text = b

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?

Community
  • 1
  • 1
DauleDK
  • 3,313
  • 11
  • 55
  • 98

1 Answers1

2

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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360