0

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?

Improve this question
1
  • Human error >.< should be date Commented Aug 26, 2010 at 11:28

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

add limit 1 to query

Improve this answer

Comments

0

Are you looking for the LIMIT statement?

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.