RE: [PHP-DB] select distinct with all columns
| From: | USA) | Date: | Mon, 12 Aug 2002 18:28:21 +0000 |
| Subject: | RE: [PHP-DB] select distinct with all columns | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-21115@lists.php.net to get a copy of this message | ||
Sorry, I wasn't thinking... you actually want to do something like this:
select type, max(id) as id, max(staff_member) as staff_member, max(class) as class,
max(manufacturer) as manufacturer, max(model) as model,
max(description) as description, max(picture) as picture, max(created) as created, max(modified) as
modified, max(published) as published
from equipment
group by type
the reason is that when you do distinct it applys to the whole select list. Which makes sense, you
may want a distinct combination of type & description for instance. So group by is what you
want. Max, may or not be the correct function to use depending on what your need is.
<>< Ryan
-----Original Message-----
From: Ryan Jameson (USA)
Sent: Monday, August 12, 2002 12:22 PM
To: php-db@lists.php.net
Subject: RE: [PHP-DB] select distinct with all columns
SELECT DISTINCT type,* FROM equipement WHERE class = "microphones";
<>< Ryan
-----Original Message-----
From: russ arbuthnot [mailto:russ3@southern.com]
Sent: Monday, August 12, 2002 12:07 PM
To: php-db@lists.php.net
Subject: [PHP-DB] select distinct with all columns
I have a mysql table named "equipment" with 11 columns named: id,
staff_member, class, type, manufacturer, model, description, picture,
created, modified, and published.
I'm trying to write a select statement similar to this:
SELECT DISTINCT type FROM equipement WHERE class = "microphones";
yet shows all 11 columns of the selected rows rather than just the
type column.
The only way I know how to show all the columns is to use SELECT *, or to
list all the columns manually like "SELECT id, type, class, ... etc."
But when I tried doing this:
SELECT DISTINCT type, id, staff_member, class, manufacturer, model,
description, picture, created, modified, published FROM equipment WHERE
class = "microphones";
I just got the exact same answer as if i would have done this:
SELECT * FROM equipment WHERE class = "microphones";
so it didn't help.
Can anyone offer a hint?
Thanks,
russ
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php