CODEWITHSUNDEEP
mysql
Charl
Charl

Reputation: 161

MySQL DISTINCT by one column

How can I get the DISTINCT function looking at only one column?

SELECT DISTINCT
    id AS uniqueid,
    ProductCode AS ProductID,
    DisplayName AS ReportingLabel,
    REPLACE(URL, 'http', 'https') AS URL,
    ProductCategory AS Category,
    Description,
    CONCAT('https://', ImageURL) AS Image,
    latestSellingPrice AS Price,
    Brand,
    IF(latestSellingPrice > 300,
        'TRUE',
        'FALSE') AS Is_Default,
    IF(StockAvailable > 0, 'TRUE', 'FALSE') AS InStock
FROM
    XML_Import_Server
WHERE
    Brand = 'Totalsports' 
AND 
    DisplayName <> 'eGift Card'

The ProductCode's aren't always unique because of size and colour options. But I only need to pull the first variant of it.

Upvotes: 1

Views: 798

Answers (2)

O. Jones
O. Jones

Reputation: 108851

It's not clear what you mean by first variant. So I will guess that you mean the variant with the lowest id value.

Do this with a subquery joined to your main table. The subquery finds the id values that are relevant.

                    SELECT MIN(id) id
                      FROM XML_Import_Server
                     GROUP BY ProductCode

Try this to convince yourself it gives you the right choice for each different ProductCode value.

Then, join that to your main query.

SELECT DISTINCT
    a.id AS uniqueid,
    a.whatever,
    a.whatever
FROM
    XML_Import_Server a
JOIN (
                        SELECT MIN(id) id
                          FROM XML_Import_Server
                         GROUP BY ProductCode

     ) b ON a.id = b.id
WHERE
    a.whatever = whatever

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

you can use aggregatio function and group by eg for some columns 

SELECT DISTINCT
  id AS uniqueid,
  min(ProductCode) AS ProductID,
  DisplayName AS ReportingLabel

FROM
    XML_Import_Server
WHERE
    Brand = 'Totalsports' 
AND 
    DisplayName <> 'eGift Card'    
group by id, DisplayName

Upvotes: 1

Related Questions