CODEWITHSUNDEEP
mysqlselect
Will B.
Will B.

Reputation: 18416

Select previous record if criteria is greater than returned value

I am trying to do a point in time query to return the quantity last recorded in that point in time.

Basically I have a table formatted as such

id | other_id | quantity | created
 1     1           5       20
 2     1           2       22
 3     1           1       25
 4     1           8       90

If the created queried was 60 I want to return the quantities for the created of 25 and higher Or from the most recently known quantity since the queried created.

SELECT * FROM table
WHERE created >= 60
AND other_id = 1
ORDER BY ID ASC

Which returns ID:4 but I want the created at 60 which was ID:3 But if the queried created was 25 I want to return 25 and greater

Brain is fried atm and unable to come up with a correct solution

Upvotes: 1

Views: 283

Answers (2)

Mark Byers
Mark Byers

Reputation: 838116

This query selects the value before the value you want:

SELECT MAX(created)
FROM yourtable
WHERE other_id = 1
AND created <= 60

You can then use this as a subquery inside your main query:

SELECT *
FROM yourtable
WHERE created >=
(
    SELECT IFNULL(MAX(created), -1)
    FROM yourtable
    WHERE other_id = 1
    AND created <= 60
)
AND other_id = 1
ORDER BY ID ASC

Upvotes: 1

Wrikken
Wrikken

Reputation: 70460

SELECT * FROM table
WHERE id >= (
  SELECT id FROM table WHERE created <= 60
  AND other_id = 1
  ORDER BY id DESC LIMIT 1
)
AND other_id = 1
ORDER BY id ASC

Upvotes: 1

Related Questions