SQL Help

[RLW]

Hi,

I think I'm having a brain fart this morning.

I have a query with output something like:

Country - Position - Date
===================
Australia - #1 - 2010-03-07
Australia - #1 - 2010-03-08
Australia - #3 - 2010-03-09
Australia - #3 - 2010-03-10
Australia - #3 - 2010-03-11
Austria - #13 - 2010-03-07
Austria - #17 - 2010-03-08
Austria - #11 - 2010-03-09
Austria - #9 - 2010-03-10
Austria - #11 - 2010-03-11
Belgium - #19 - 2010-03-07
Belgium - #17 - 2010-03-08
Belgium - #13 - 2010-03-09
Belgium - #11 - 2010-03-10
Belgium - #9 - 2010-03-11
Canada - #36 - 2010-03-07
Canada - #36 - 2010-03-08
Canada - #25 - 2010-03-09
Canada - #20 - 2010-03-10
Canada - #11 - 2010-03-11
Denmark - #11 - 2010-03-07
Denmark - #9 - 2010-03-08
Denmark - #7 - 2010-03-09
Denmark - #9 - 2010-03-10
Denmark - #7 - 2010-03-11
Finland - #13 - 2010-03-07
Finland - #13 - 2010-03-08
Finland - #15 - 2010-03-09
Finland - #11 - 2010-03-10
Finland - #15 - 2010-03-11
France - #3 - 2010-03-07
France - #5 - 2010-03-08
France - #3 - 2010-03-09
France - #3 - 2010-03-10
France - #3 - 2010-03-11
Germany - #7 - 2010-03-07
Germany - #11 - 2010-03-08
Germany - #11 - 2010-03-09
Germany - #9 - 2010-03-10
Germany - #7 - 2010-03-11
Greece - #66 - 2010-03-07
Greece - #97 - 2010-03-08
Greece - #40 - 2010-03-09
Greece - #31 - 2010-03-10
Greece - #11 - 2010-03-11
Ireland - #1 - 2010-03-07
Ireland - #1 - 2010-03-08
Ireland - #1 - 2010-03-09
Ireland - #1 - 2010-03-10
Ireland - #1 - 2010-03-11
Italy - #49 - 2010-03-07
Italy - #55 - 2010-03-08
Italy - #61 - 2010-03-09
Italy - #53 - 2010-03-10
Italy - #39 - 2010-03-11
Luxembourg - #11 - 2010-03-07
Luxembourg - #13 - 2010-03-08
Luxembourg - #15 - 2010-03-09
Luxembourg - #17 - 2010-03-10
Luxembourg - #19 - 2010-03-11
Netherlands - #33 - 2010-03-07
Netherlands - #37 - 2010-03-08
Netherlands - #37 - 2010-03-09

The query is:

SELECT position, date, name FROM mchart_itunes_charts_daily INNER JOIN mchart_itunes_countries ON mchart_itunes_charts_daily.country = mchart_itunes_countries.id WHERE song_id = '1392' AND genre = '0' AND chart_type = '' ORDER BY name

How do I cut that down to only give me the lowest position for each country, and the corresponding date?

I tried:

SELECT MIN(position) as position, date, name FROM mchart_itunes_charts_daily INNER JOIN mchart_itunes_countries ON mchart_itunes_charts_daily.country = mchart_itunes_countries.id WHERE song_id = '1392' AND genre = '0' AND chart_type = '' GROUP BY name ORDER BY name

It gives me the lowest position, but the date it doesn't give the corresponding date, just the first date that it comes across...

My head just isn't with it this morning... I'm sure I've done similar queries before with no probs... Maybe it's because I have a deadline... or maybe it's the music roaring in the background....

Anyway, can anyone help?

Thanks,
Richard

 

[RLW]

I got it working with

SELECT x.min_pos as position, x.song_album_id, name, max(date) as date FROM (SELECT song_album_id, country, min(position) as min_pos FROM `mchart_itunes_charts_daily` WHERE song_album_id = '1392' GROUP BY country) as x INNER JOIN mchart_itunes_charts_daily as y on x.song_album_id = y.song_album_id AND x.min_pos = y.position AND x.country = y.country INNER JOIN mchart_itunes_countries as z ON z.id = y.country WHERE y.song_album_id = '1392' GROUP BY y.country

Thanks to anyone who looked, but I don't need any help now