Select Distinct on one column and eliminate nulls in Select Distinct?

Question

follow this question I have...

    ID  SKU PRODUCT
    =======================
    1   FOO-23  Orange
    2   BAR-23  Orange
    3   FOO-24  Apple
    4   FOO-25  Orange
    5   FOO-25  null
    6   FOO-25  null

expected result:

1   FOO-23  Orange
3   FOO-24  Apple
5   FOO-25  null
6   FOO-25  null

This query isn't getting me there. How can I SELECT DISTINCT on just one column and eliminate null in SELECT DISTINCT?

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

Your desired output and the explanation are conflicting with each other. You state you want to eliminate NULL but you have FOO-25 in the output. I would say all you need is an OR predicate. Where a.RowNumber = 1 OR a.PRODUCT IS NULL. — Sean Lange, Jan 16 '18 at 14:46
Did you try the shot in the dark I suggested? It still seems like what you want. — Sean Lange, Jan 16 '18 at 14:48

John Cappelletti · Answer 1 · 2018-01-16T15:00:25.030

Perhaps one approach is using the WITH TIES in concert with a conditional PARTITION

Example

Declare @YourTable Table ([ID] int,[SKU] varchar(50),[PRODUCT] varchar(50))
Insert Into @YourTable Values 
 (1,'FOO-23','Orange')
,(2,'BAR-23','Orange')
,(3,'FOO-24','Apple')
,(4,'FOO-25','Orange')
,(5,'FOO-25',NULL)
,(6,'FOO-25',NULL)

Select top 1 with ties * 
 From @YourTable
 Where SKU Like 'FOO%'
 Order By Row_Number() over (Partition By IsNull(Product,NewID()) Order By ID)

Returns

ID  SKU     PRODUCT
6   FOO-25  NULL
5   FOO-25  NULL
3   FOO-24  Apple
1   FOO-23  Orange

score 2 · Accepted Answer · answered Jan 16 '18 at 14:54

2

Using John Cappelletti's sample data here is another approach. All you really needed was to add the OR predicate to your where clause.

Declare @YourTable Table ([ID] int,[SKU] varchar(50),[PRODUCT] varchar(50))
Insert Into @YourTable Values 
 (1,'FOO-23','Orange')
,(2,'BAR-23','Orange')
,(3,'FOO-24','Apple')
,(4,'FOO-25','Orange')
,(5,'FOO-25',NULL)
,(6,'FOO-25',NULL)

SELECT  *
FROM
(
    SELECT ID
        , SKU
        , Product
        , ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowNumber
    FROM   @YourTable
    WHERE  SKU LIKE 'FOO%'
) AS a
WHERE  a.RowNumber = 1
    OR a.PRODUCT IS NULL --This was the only part you were missing

answered Jan 16 '18 at 14:54

Sean Lange

33,028
3
25
40

I never even considered adding the OR +1 – John Cappelletti Jan 16 '18 at 14:57
Really Thanks, I don't want rownumber column in result – Elham Azadfar Jan 16 '18 at 15:05
Well that isn't all that difficult....don't use select *. You shouldn't be using select * anyway. You should always select ONLY those columns you need. – Sean Lange Jan 16 '18 at 15:07

score 2 · Answer 3 · answered Jan 16 '18 at 14:56

I changed your row_number to dense rank:

Declare @YourTable Table ([ID] int,[SKU] varchar(50),[PRODUCT] varchar(50))
Insert Into @YourTable Values 
 (1,'FOO-23','Orange')
,(2,'BAR-23','Orange')
,(3,'FOO-24','Apple')
,(4,'FOO-25','Orange')
,(5,'FOO-25',NULL)
,(6,'FOO-25',NULL)

SELECT  *
FROM    (SELECT ID, SKU, Product,
                Dense_RANK() OVER (PARTITION BY SKU ORDER BY Product) AS RowNumber
         FROM   @YourTable
         WHERE  left(SKU,3) = 'FOO') AS a
WHERE   a.RowNumber = 1

Results:

ID  SKU Product RowNumber
1   FOO-23  Orange  1
3   FOO-24  Apple   1
5   FOO-25  NULL    1
6   FOO-25  NULL    1

DENSE_RANK basically allows ties and gives them the same weight and keeps a numerical order (1,1,2,3). RANK would also work and works like the olympics (1,1,3) — KeithL, Jan 16 '18 at 15:20

Select Distinct on one column and eliminate nulls in Select Distinct?

3 Answers3