How to select more then one field using the DISTINCT keyword

[diezy]

hey

i was wondering if there is a way to select more then on field using the DISTINCT keyword

i have a query like this:
$str_sql = "SELECT id, product_id, name, description, price FROM products ORDER BY name ASC";

However i know adding the Distinct keyword like this won't work

$str_sql = "SELECT id, product_id, DISTINCT(name), description, price FROM products ORDER BY name ASC";


any help would be appreciated

thanks

 

[LTeeple]

Have you tried:

$str_sql = "SELECT DISTINCT name, id, product_id, description, price FROM products ORDER BY name ASC";?

That syntax works for me...


Cheers!
Laura

 

[r937]

DISTINCT is not a function, it is a keyword which ensures that result rows are unique

could you give an example of how you want more than one column distinct?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[diezy]

actually it got it to work like this

$str_sql = "SELECT id, product_id, name, description, price FROM products GROUP BY name ORDER BY name ASC";

 

[r937]

watch out, that syntax is invalid, but mysql will execute it anyway!!

however, they do warn you that you may get unpredictable results

please see 12.9.3. GROUP BY with Hidden Fields

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[diezy]

oh really eh?...i just read the clip.
ok i have this product table, full of 10 products records (let just say) now each product belongs to a category. However, there are some products that are in more the one category. So really there are 30 product records in the the products table. So, on the site there is a link for ALL categories. So, what i want when ppl click on the link is to show all the unique products in the products table.

Now heres the thing....there is more then one column in the products table that has the same data

for instance:

id product_id name cost category
1 b_013 big_basket 13.00 weddings
2 b_013 big_basket 13.00 weddings
1 b_014 baby_basket 6.00 baby
2 b_014 baby_basket 6.00 baby

 

[r937]

your data looks bad

i'd have a hard look at whatever logic is allowing dupes to get into your table





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[diezy]

sorry my i entered in my data wrong

however please this me is this is wrong too and how i should go it correctly

id product_id name cost category
1 b_013 big_basket 13.00 weddings
2 b_013 big_basket 13.00 weddings
3 b_014 baby_basket 6.00 baby
4 b_014 baby_basket 6.00 baby