MySQL: Select distict field, but get the rest of the columns as well?

Question

I want to select distinct product_series, but I want all the other columns too.

This is my query as it stands:

SELECT DISTINCT product_series 
FROM cart_product 
WHERE product_brand = "everlon" 
AND product_type = "ring" 
AND product_available = "yes"

But this only gives me product_series, but I need all the other columns in that row too. But if I try to select more than just product_series, I end up getting multiples of product series.

So basically what I want is * all the fields but I want to limit it so i only get 1 row per product series.

I am not sure if I am explaining this correctly so let me give an example:

if I have

product_series   product_id
----------------------------
"seriesA"        230
"seriesA"        231
"seriesB"        232
"seriesB"        233

I would get all the columns but only 1 per product_series:

product_series   product_id
----------------------------
"seriesA"        230
"seriesB"        232

How can I do that?

Answer 1

Sounds like you want GROUP BY instead.

Answer 2

You can use GROUP BY to do that. But please realize that there has to be some way of "flattening" the group of multiple product_id's int a single value. This is achieved with an aggregate function, like MIN, MAX, or GROUP_CONCAT():

SELECT product_series, MAX(product_id) max_product_id
FROM cart_product 
WHERE product_brand = 'everlon'
AND product_type = 'ring'
AND product_available = 'yes'
GROUP BY product_series

BTW: please don't use double quotes in SQL to delimit strings. In all RDBMS-es but MySQL the double quote can be used to delimit identifiers, and only single quotes can be used to delimit string literals.

Answer 3

SELECT  pi.*
FROM    (
        SELECT  DISTINCT product_series 
        FROM    cart_product 
        ) pd
JOIN    cart_product  pi
ON      pi.id =
        (
        SELECT  id
        FROM    cart_product po
        WHERE   product_brand = "everlon" 
                AND product_type = "ring" 
                AND product_available = "yes"
                AND po.product_series = pd.product_series
        LIMIT 1
        )

This will pick one product per series in no particular order.

Add an ORDER BY condition into the subquery to define the order.

You may also want to read this article in my blog:

• MySQL: Selecting records holding group-wise maximum (resolving ties)