[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
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-DB] Re: select distinct with all columns
SELECT * FROM equipement WHERE class = microphones GROUP BY type; 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
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] best way to stare true, false
if you are storing a bunch of T/F, you can store 8 of them into a single byte... each bit position will represent one question... you just have to do some BIT manipulation on your end... andy wrote: Hi there, I am searching for the best way to store true or false inside a MySQL DB. With best I mean the most data efficient way and in the same time the best for fast searching later on the table to find all which are false, or true. I think I did read something about a way to do this with ENUM, but cant remember where and how, all tryes did not succeeed so far. -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php