Search code examples
mysqlsqlaggregate-functionsgreatest-n-per-group

Fetch records with MAX values between specific timestamp in accurate MAX order

I am having some challenges spooling out some database records.

I need to get the rows with MAX value for a specific column and these records must fall between two timestamp values.

Here is the SQL query

SELECT id, MAX(amount), created 
FROM `product` 
where author = '1' AND (created BETWEEN '2018-02-03' AND '2018-02-08') 
GROUP BY id

I am able to get the records with MAX value in an accurate order using

SELECT id, MAX(amount), created FROM `product` where author = '1' GROUP BY id

But the moment I include a clause to distinct records between my desired timestamp, I lose the accurate order in MAX

Would be really glad to get some help with this. Thanks

Solution

  • GROUP BY then ORDER BY MAX(amount)

    SELECT id, MAX(amount), created 
FROM `product` 
where author = '1' AND (created BETWEEN '2018-02-03' AND '2018-02-08') 
GROUP BY id
ORDER BY 2 DESC