By mmcglynn


2009-06-08 18:12:40 8 Comments

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]

7 comments

@user3070485 2019-03-19 16:27:13

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.

@Anna Karthi 2013-12-14 04:53:48

Try this:

SELECT * FROM [TestData] WHERE Id IN(SELECT DISTINCT MIN(Id) FROM [TestData] GROUP BY Product)   

@DoodleKana 2014-04-10 23:20:50

that returns no result. Does not work

@Bartosz X 2015-11-18 10:24:28

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  

@Jakob Egger 2012-03-28 08:17:33

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]
)

@KM. 2009-06-08 18:16:12

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

@Lecko 2009-06-08 20:01:04

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'

@Carl Manaster 2009-06-08 20:17:32

Was going to +1 this, because I think GROUP BY is the right way to go - but the minimum ID and the minimum SKU may not happen to belong to the same record. It's hard to determine what are the correct ID and SKU to report for a given PRODUCT.

@Aaron Alton 2009-06-08 18:20:09

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

@Mark Byers 2011-12-07 09:13:56

You aren't using a CTE in your query. That's just a derived table. But you are right that you could have used a CTE here.

@Andre Nel 2017-06-21 11:46:47

leave out "AS" for oracle -> ...WHERE SKU LIKE 'FOO%') a WHERE a.RowNumber = 1

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

26 Answered Questions

9 Answered Questions

[SOLVED] SQL to find the number of distinct values in a column

  • 2008-09-26 19:52:48
  • Christian Oudard
  • 487406 View
  • 311 Score
  • 9 Answer
  • Tags:   sql distinct

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

20 Answered Questions

[SOLVED] LINQ's Distinct() on a particular property

7 Answered Questions

[SOLVED] SQL Server query - Selecting COUNT(*) with DISTINCT

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

37 Answered Questions

Sponsored Content