mysql Max(date) value in returned rows

Question

in mytable when i query

SELECT * FROM mytable WHERE adsh='0000002178-18-000009' and tag='assets'

i get this result

adsh tag ddate value
0000002178-18-000009 Assets 2016-12-31 246872000.00
0000002178-18-000009 Assets 2017-12-31 282704000.00

but i wish to have only the row containing the max(ddate) returned, the 2017-12-31 row Note there are many other different tags. But since the table holds >100k rows i wish to make sure i am doing the right query before expanding it to all rows.

I tried many different queries & variations but no cigar :/

SELECT *,max(ddate) FROM mytable WHERE adsh='0000002178-18-000009' and tag='Assets'

that return the wrong line

SELECT * FROM mytable
WHERE ddate = (select max(ddate) and adsh='0000002178-18-000009' and tag='Assets' from mytable)

that returns 0 rows

SELECT * FROM mytable
WHERE ddate = (select max(ddate) and adsh='0000002178-18-000009' and tag='Assets' from mytable)

that returns 0 rows

SELECT DISTINCT adsh,tag,ddate,value from mytable
WHERE ddate = (select max(ddate) from mytable) group by adsh

but that's not returning what i expect either

Does anyone have a idea how i can acheive this ?

Thanks a million :)

For one definite tag use simple ```SELECT * FROM mytable WHERE adsh='0000002178-18-000009' AND tag='assets' ORDER BY dddate DESC LIMIT 1``` — Akina, Dec 14 '20 at 18:08

score 0 · Answer 1 · answered Dec 14 '20 at 17:45

You seem to want:

select * 
from mytable
where ddate = (
    select max(ddate) 
    from mytable
    where adsh='0000002178-18-000009' and tag='Assets' 
)

You would get something more accurate by repeating the conditions in the where clause of the outer query:

select * 
from mytable
where adsh = '0000002178-18-000009' and tag = 'Assets'  and ddate = (
    select max(ddate) 
    from mytable
    where adsh = '0000002178-18-000009' and tag = 'Assets' 
)

Just in case, let me pinpoint that if you are sure that are no top ties, this can be simpler done with limit:

select * 
from mytable
where adsh = '0000002178-18-000009' and tag = 'Assets'
order by dddate desc limit 1

Finally: if you are running MySQL 8.0, you can also use window functions:

select *
from (
    select t.*,
        rank() over(order by ddate desc) as rn
    from mytable t
    where adsh = '0000002178-18-000009' and tag = 'Assets'
) t
where rn = 1

Thanks, i was able to figure it out with the help of this post :) [https://stackoverflow.com/questions/28563100/return-corresponding-date-of-max-value](https://stackoverflow.com/questions/28563100/return-corresponding-date-of-max-value) — Paul, Dec 16 '20 at 09:07

mysql Max(date) value in returned rows

1 Answers1