SELECT DISTINCT on one column

Question

Using SQL Server  I have     ID  SKU     PRODUCT                         1   FOO-23  Orange 2   BAR-23  Orange 3   FOO-24  Apple 4   FOO-25  Orange   I want   1   FOO-23  Orange 3   FOO-24  Apple   This query isn t getting me there  How can I SELECT DISTINCT on just one column   SELECT   ID   SKU   PRODUCT  FROM  TestData   WHERE   PRODUCT      SELECT DISTINCT  PRODUCT  FROM  TestData  WHERE   SKU  LIKE  FOO-      ORDER BY  ID

User · Answer

SELECT min  id  AS  ID   min sku  AS  SKU   Product     FROM TestData     WHERE sku LIKE  FOO   -- If you want only the sku that matchs with FOO      GROUP BY product      ORDER BY  ID

User · Answer

The simplest solution would be to use a subquery for finding the minimum ID matching your query  In the subquery you use GROUP BY instead of DISTINCT   SELECT   FROM  TestData  WHERE  ID  IN      SELECT MIN  ID   FROM  TestData     WHERE  SKU  LIKE  FOO-      GROUP BY  PRODUCT

User · Answer

Try this   SELECT   FROM  TestData  WHERE Id IN SELECT DISTINCT MIN Id  FROM  TestData  GROUP BY Product   nbsp  nbsp

User · Answer

Here is a version  basically the same as a couple of the other answers  but that you can copy paste into your SQL server Management Studio to test   and without generating any unwanted tables   thanks to some inline values   WITH  TestData   ID   SKU   PRODUCT   AS       SELECT       FROM           VALUES          1     FOO-23     Orange             2     BAR-23     Orange             3     FOO-24     Apple             4     FOO-25     Orange             AS  TestData   ID   SKU   PRODUCT      SELECT   FROM  TestData  WHERE  ID  IN        SELECT MIN  ID        FROM  TestData       GROUP BY  PRODUCT      Result  ID  SKU     PRODUCT 1   FOO-23  Orange 3   FOO-24  Apple   I have ignored the following      WHERE   SKU  LIKE  FOO-      as its only part of the authors faulty code and not part of the question  It s unlikely to be helpful to people looking here

User · Answer

try this   SELECT      t       FROM TestData t         INNER JOIN  SELECT                         MIN ID  as MinID                         FROM TestData                         WHERE SKU LIKE  FOO-                        dt ON t ID dt MinID   EDIT once the OP corrected his samle output  previously had only ONE result row  now has all shown   this is the correct query   declare  TestData table  ID int  sku char 6   product varchar 15   insert into  TestData values  1     FOO-23         Orange   insert into  TestData values  2     BAR-23         Orange   insert into  TestData values  3     FOO-24         Apple   insert into  TestData values  4     FOO-25         Orange    --basically the same as  Aaron Alton s answer  SELECT     dt ID  dt SKU  dt Product     FROM  SELECT               ID  SKU  Product  ROW NUMBER   OVER  PARTITION BY PRODUCT ORDER BY ID  AS RowID               FROM  TestData               WHERE  SKU LIKE  FOO-              AS dt     WHERE dt RowID 1     ORDER BY dt ID

User · Answer

Assuming that you re on SQL Server 2005 or greater  you can use a CTE with ROW NUMBER     SELECT    FROM     SELECT ID  SKU  Product                  ROW NUMBER   OVER  PARTITION BY PRODUCT ORDER BY ID  AS RowNumber          FROM   MyTable          WHERE  SKU LIKE  FOO    AS a WHERE   a RowNumber   1

User · Answer

I know it was asked over 6 years ago  but knowledge is still knowledge  This is different solution than all above  as I had to run it under SQL Server 2000   DECLARE  TestData TABLE  ID  int   SKU  char 6    Product  varchar 15   INSERT INTO  TestData values  1   FOO-23    Orange   INSERT INTO  TestData values  2   BAR-23    Orange   INSERT INTO  TestData values  3   FOO-24    Apple   INSERT INTO  TestData values  4   FOO-25    Orange    SELECT DISTINCT   ID      SELECT TOP 1  ID   FROM  TestData Y WHERE Y  Product    X  Product                     SKU     SELECT TOP 1  SKU  FROM  TestData Y WHERE Y  Product    X  Product                     PRODUCT               FROM  TestData X

[sql] SELECT DISTINCT on one column

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to distinct