query question

[andars]

I have a database, and I want to select all elements, but I only want some to be distinct.

$SQL = "SELECT DISTINCT TITLE, ALBUM, ARTIST, DATE FROM ". DB_PLAYING ." WHERE `USERID`='{$USER["ID"]}' ORDER BY `DATE` DESC LIMIT $start_page, $shown_rows";

I want the title, album, and artist to all be distinct, but not the date/timestamp and I'm not sure how to do what I want in one query. I still need the date info retrieved however.

Basically I need to select distinct and nondistinct info in the same query.

Thanks for any help.

[Sxooter]

So, assuming that there might be more than one entry that has those particular fields the same (title, artist, et. al.) WHICH date would you want?

Easy way is to do:

SELECT TITLE, ALBUM, ARTIST, max(DATE) from table group by TITLE, ALBUM, ARTIST

which will give the highest date for that grouping.

Note that MySQL will let you do this without the max(), but it's not portable to any other databases because that goes against the SQL spec. Also, PostgreSQL has a distinct on feature:

SELECT distinct on (TITLE, ALBUM, ARTIST) TITLE, ALBUM, ARTIST, DATE from table

which is also non-transportable, and basically does the same thing as MySQL's group by without an aggregate.

[tomhath]

I assume what you have is a title, album, artist with multiple (but identical) dates?

If so, you'll have to return all the rows without the distinct and handle it in code. You can't get one title/albut/artist row with multiple dates.