MYSQL DISTINCT - selecting some values that are distinct, some that aren't?

Hi,

How would I go around doing that?

Lets say for example I have a table with fields like this:

id, author, book, rating (1-5), published (yes, no)

Now if I wanted to extract all different authors, but extract all the other information with it I can't just use distinct on the whole selection like:

SELECT DISTINCT * FROM table

because it would just return two results - one that is published and one that isn't.

Is there a way to select where only one field is distinct (i.e. the author field)?

The id and book fields would always be distinct anyway with a certain author so they could be extracted with the author distinctly. It's the rating and published fields that are tripping me up.

See if its something simple and i'm just overcomplicating things...that'll be annoying.

Anyway,
Thanks,
TS

 

SELECT * FROM my_table GROUP BY author;

This would return only one row for each unique author.

If you want an author row with certain criteria, you can still use Order By to get the author result with the highest rating, or something else.

SELECT * FROM my_table GROUP BY author ORDER BY rating DESC;

 

Thanks. Guess I was focusing on trying to get it to work with distinct, totally forgot about using group by.

Thanks again!
TS