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

Question

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

What does "accurate MAX order" mean? Can you post some sample data and the desired result? — Barmar, Feb 10 '18 at 01:43

score 0 · Accepted Answer · answered Feb 10 '18 at 01:44

0

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

answered Feb 10 '18 at 01:44

D-Shih

Thanks a bunch.. The `ORDER BY 1 DESC` was the game changer and I really like the approach and It just made me understood some basic things regarding record ordering in mysql... It simply changed from the default `ORDER BY id ASC` to yours... Thanks a million... – Ous Feb 10 '18 at 02:30

1 Answers1