How do you SELECT *, when you want to select DISTINCT?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you SELECT *, when you want to select DISTINCT?

Post by simonmlewis »

$result = mysql_query ("SELECT *, DISTINCT category FROM products WHERE pause = 'off' ORDER BY category ASC");

This doesn't work. And most SQL wizards here will probably see why.
I need to select everything from the row, where the DISTINCT query products a result.

ie, if there are 5 results (out of 50 rows for ex.), it has has all fields from it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do you SELECT *, when you want to select DISTINCT?

Post by Eran »

DISTINCT selects distinct values in a group, not distinct rows. You need to use GROUP BY with some additional filtering
Post Reply