trying to get distinct results using 'group by'

greg890

Hi,

I hope someone can help me. I want to get distinct results from a query. I'm using:

$sql = "select music_songs.musician_userid,song_datetime,paid_member,display_name,song_title from music_songs,music_musician where music_songs.musician_userid = music_musician.musician_userid && paid_member = 1  group by music_songs.musician_userid order by music_songs.song_datetime desc";

That query seems to ignore a lot of the results that should be there. If I take out the group by part, it gives all the correct results, but it shows the same musician_userid more than once. How can I correctly get distinct results on the musician_userid?

Thanks,

Greg

bogu

greg890 wrote:
That query seems to ignore a lot of the results

What do u mean by "ignore a lot of results", if u use group by it doesn't extract all the musician_userid from music_songs that are in the music_musician too and have paid_member equal 1?

greg890

no. It does not. There are many musicians that fit the criterea I am querying, that it is completely skipping over, so I thought there must be an error in my query.

For example John Doe is in my musicians and songs table and he has a paid member value of 1.

When I leave out group by, it shows his result. When I include group by, it does not show his result.

I've tried three different queries that appear correct and they have all skipped the same results that should be there. So I'm going to read about subqueries and try and write something new.

Piranha

Use the word AND instead of && in SQL queries.

greg890

Thanks.

The problem I have is being caused by the order by part of the query. It is choosing any song and grouping it with the musician_userid, but I need it to also to select by the song that has the greatest value (the most current song).

So instead of having an output like:

Dave - Song1 - Jan 1, 2000
John - Song1 - Jan 1, 2000

I would have an output like

Dave - Song 34 - Nov 14, 2006
John - Song 13 - Feb 24, 2006

Maybe I need to write a subquery or use GREATEST, or something. Any ideas?

Thanks again,

Greg