CODEWITHSUNDEEP
sql-server-2008no-duplicatespartition-by
Felicia Soh
Felicia Soh

Reputation: 865

How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned

I have used multiple columns in Partition By statement in SQL but duplicate rows are returned back. I only want distinct rows being returned back.

This is what I have coded in Partition By:

SELECT DATE, STATUS, TITLE, ROW_NUMBER() OVER (PARTITION BY DATE, STATUS, TITLE ORDER BY QUANTITY ASC) AS Row_Num
    FROM TABLE

This is the output I get currently: (Where there are duplicate rows being returned - Please Refer to Row 6 to 8)

enter image description here

This is the output I want to achieve: (no duplicate row being returned - Please Refer to Row 6 to 8)

enter image description here

Question: How can I place multiple columns in 1 Partition By and Ensure No Duplicate Row is Returned?

Appreciate if someone can provide me help on this, thanks a lot!!

Upvotes: 48

Views: 328975

Answers (5)

DMB
DMB

Reputation: 1

DENSE_RANK () works better with multiple fields in the partition. syntax is the same.

Upvotes: 0

Dave Welden
Dave Welden

Reputation: 1918

Try DENSE_RANK() instead of ROW_NUMBER() and select for DR = 1

Upvotes: 0

Andrew Troiano
Andrew Troiano

Reputation: 187

I'd create a cte and do an inner join. It's not efficient but it's convenient

with table as (
SELECT DATE, STATUS, TITLE, ROW_NUMBER() 
OVER (PARTITION BY DATE, STATUS,  TITLE ORDER BY QUANTITY ASC) AS Row_Num
 FROM TABLE)

select *

from table t
join select(
max(Row_Num) as Row_Num
,DATE
,STATUS
,TITLE
from table 
group by date, status, title) t2  
on t2.Row_Num = t.Row_Num and t2
and t2.date = t.date
and t2.title = t.title

Upvotes: 3

Ankit Raonka
Ankit Raonka

Reputation: 6859

Try this, It worked for me

SELECT * FROM (
            SELECT
                [Code],
                [Name],
                [CategoryCode],
                [CreatedDate],
                [ModifiedDate],
                [CreatedBy],
                [ModifiedBy],
                [IsActive],
                ROW_NUMBER() OVER(PARTITION BY [Code],[Name],[CategoryCode] ORDER BY ID DESC) rownumber
            FROM MasterTable
          ) a
        WHERE rownumber = 1

Upvotes: 45

Bhavesh Harsora
Bhavesh Harsora

Reputation: 693

If your table columns contains duplicate data and If you directly apply row_ number() and create PARTITION on column, there is chance to have result in duplicated row and with row number value.

To remove duplicate row, you need one more INNER query in from clause which eliminates duplicate rows and then it will give output to it's foremost outer FROM clause where you can apply PARTITION and ROW_NUMBER ().

As like below example:

SELECT DATE, STATUS, TITLE, ROW_NUMBER() OVER (PARTITION BY DATE, STATUS, TITLE ORDER BY QUANTITY ASC) AS Row_Num
FROM (
     SELECT DISTINCT <column names>...
) AS tbl

Upvotes: 15

Related Questions