Q:

Self inner join to get single record

I have an SQL table data as follow
 
I want to display single record for product
example
90792 Amlaan-Hi-Power .............. Show only 1 record when there are 2 record 
90793 Amlaan-Neutral  .............. show only 1 record when there are 2 record 
90794 Amlaan-Phosphate free .........show only 1 record when there are 2 record  
90801 Acetone .......................show only 1 record when there are 2 record 
90901 Acetanilide ...................show only 1 record when there is 1 record 

Can I do this using Inner join
I know 
select distinct product from product ORDER BY `product`.`product` DESC

will select distinct (unique) product code and that to only one field i.e. product but confused how to get other information  using SQL statement
but results in duplicate records or same table...........................

A:

It looks like your duplicate rows vary by the quantity of product in the package.
You can display just the product and name with
SELECT DISTINCT product, name
  FROM product

If you want to deal with the quantity as well, that's a little trickier. This might work: it will put all product codes on one line.
 SELECT product,
        GROUP_CONCAT(product_code ORDER BY product_code) product_codes,
        name
   FROM product
  GROUP BY product, name

Self join doesn't make a whole lot of sense for this application.

