[PHP-DB] oops...Re: [PHP-DB] select distinct with all columns
oops, i accidentally sent it without finishing... anyway, what i wanted to say was DISTINCT, at least how i learned it, was working with rows instead of colums: http://www.mysql.com/doc/en/DISTINCT_optimisation.html is there a reason why you dont want to list the columns? russ arbuthnot wrote: > 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 -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select distinct with all columns
you maybe using it the wrong way. read the doc: russ arbuthnot wrote: > 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 -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] select distinct with all columns
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: [EMAIL PROTECTED] Subject: RE: [PHP-DB] select distinct with all columns SELECT DISTINCT type,* FROM equipement WHERE class = "microphones"; <>< Ryan -Original Message- From: russ arbuthnot [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 12:07 PM To: [EMAIL PROTECTED] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] select distinct with all columns
SELECT DISTINCT type,* FROM equipement WHERE class = "microphones"; <>< Ryan -Original Message- From: russ arbuthnot [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 12:07 PM To: [EMAIL PROTECTED] 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
