randombits randombits - 4 months ago 7
SQL Question

Does this aggregate query require a subquery in mysql?

I have a table that has the following columns:

user_id int
,
some_dt datetime
,
count int


None of these are unique keys.

I'm trying to get the
time
and
count
values for a
user_id
where
count
is at the highest. a sample query will look like the following to get the highest count for
user_id
100 on July 18th, 2016:

SELECT MAX(count) FROM tbl
WHERE user_id = 100
AND DATE(some_dt) = '2016-07-18'


My problem is, I also want to get the
some_dt
value for the row
MAX(count)
came in. Is there a way to do this without multiple queries?

Answer

Could be you can use having clause

SELECT count, some_dt FROM tbl
WHERE user_id = 100
AND DATE(some_dt) = '2016-07-18'
having count = max(count);

otherwise you need a subquery for max

SELECT count, some_dt FROM tbl
WHERE user_id = 100
AND DATE(some_dt) = '2016-07-18'
and count = select (max(count) FROM tbl
            WHERE user_id = 100
            AND DATE(some_dt) = '2016-07-18');
Comments