i am new in learning sql. how to create query to get the timestamp of a minimum value and the minimum value itself? previously i managed to get the minimum value but not with its timestamp. with this query
SELECT min(score) as lowest
FROM rank
WHERE time >= CAST(CURDATE() AS DATE)
here is the table that i've created:
(cannot attach image because of the reputation rule)
sorry for the bad english.
If you either expect that there would be only a single record with the lowest score, or if there be ties, you don't care which record gets returned, then using LIMIT might be the easiest way to go here:
SELECT timestamp, score
FROM rank
WHERE time >= CAST(CURDATE() AS DATE)
ORDER BY score
LIMIT 1;
If you care about ties, and want to see all of them, then we can use a subquery:
SELECT timestamp, score
FROM rank
WHERE time >= CAST(CURDATE() AS DATE) AND
score = (SELECT MIN(score) FROM rank WHERE time >= CAST(CURDATE() AS DATE));
It's possible by following way.
Note: It only works if you want to get a single record at once
select score, time
FROM rank
WHERE time >= CAST(CURDATE() AS DATE)
ORDER BY score ASC LIMIT 1
