MySQL Select query - remove unwanted rows

Question

I have a table with the following (I've sorted it by date):

ID        prodId    date
16      532     2015-08-17
19      535     2014-08-18
18      534     2011-08-17
27      48      2010-08-26
26      1541    2010-08-25
25      1541    2010-08-21
24      1540    2010-08-20
21      48      2010-08-19
20      48      2010-08-18
17      533     2010-08-17
14      532     2010-08-17
22      1540    1970-01-01

I want to select the most recent prodId whos date is in the past. My problem is I am getting multiple prodId with the same value (in this example, 48 and 1541).

My query is currently:

SELECT * FROM `prods` WHERE (date <= '2010-08-26')  order by date DESC

How do I change my query to remove the unwanted rows?

Answer 1

add limit 1 to query

Answer 2

SELECT * FROM prods p1
WHERE (date <= '2010-08-26') 
AND Date in (Select Max(Date) from prods p2 where p1.prodId = pr.ProdId 
             and date <= '2010-08-26')
order by activeUntil DESC

Answer 3

Are you looking for the LIMIT statement?

SELECT * FROM `prods` WHERE (date <= '2010-08-26')  order by activeUntil DESC LIMIT 1