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

Question

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)

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

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!!

Cathepsin

Answer 1

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 

Aeon

Why is it that you have to name all the columns instead of using * for the interior SELECT statement? – Chenault

Answer 2

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

Zygapophysis

hi @Bhavesh Harsora, have tried this method but doesn't work. This is what I have tried: SELECT tbl.DATE, STATUS, tbl.TITLE, ROW_NUMBER() OVER (PARTITION BY DATE, STATUS, TITLE ORDER BY QUANTITY ASC) AS Row_Num FROM (SELECT DISTINCT TITLE FROM TABLE) AS tbl – Cathepsin

Your given query is missing {tbl.DATE, STATUS} columns From your Inner Query you specified where you have taken only one TITLE column. You can also try an alternative way like removing duplicated value using GROUP BY and HAVING clause. – Zygapophysis

hi @Bhavesh Harsora, have tried on this two methods but do not work as well. I tried to add in the missing columns that you have mentioned but still same rows bring returned. Then next I tried to use GROUP BY and HAVING clause inside the inner query but still the same rows being returned as well. – Cathepsin

Answer 3

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

Ambler

Answer 4

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

Acadian

Answer 5

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

Rhizomorphous

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Elderberry