hello, I am out of any logic how to write the right sql statment. I've got a sqlite table holding every played track in a row with played date/time Now I will count the plays of all artists, grouped by day and then find the artist with the max playcount per day. I used this Query

SELECT COUNT(ARTISTID) AS artistcount,
ARTIST AS artistname,strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname

to get this result

"93"|"The Skygreen Leopards"|"2010-06-16"
"2" |"Arcade Fire"          |"2010-06-15"
"2" |"Dead Kennedys"        |"2010-06-15"
"2" |"Wolf People"          |"2010-06-15"
"3" |"16 Horsepower"        |"2010-06-15"
"3" |"Alela Diane"          |"2010-06-15"
"46"|"Motorama"             |"2010-06-15"
"1" |"Ariel Pink's Haunted" |"2010-06-14"

I tried then to query this virtual table but I always get false results in artistname.

SELECT MAX(artistcount), artistname , day_played 
FROM
(
SELECT COUNT(ARTISTID) AS artistcount,
ARTIST AS artistname,strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname
)
GROUP BY strftime('%Y-%m-%d',day_played)

result in this

"93"|"lilium"     |"2010-06-16"
"46"|"Wolf People"|"2010-06-15"
"30"|"of Montreal"|"2010-06-14"

but the artist name is false. I think through the grouping by day, it just use the last artist, or so. I tested stuff like INNER JOIN or GROUP BY ... HAVING in trial and error, I read examples of similar issues but always get lost in columnnames and stuff (I am a bit burned out)

I hope someone can give me a hint. thanks m