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

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi Members,

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:

SQL

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

This is the output I get currently: (Where there are duplicate rows being returned - Refer to Row 6 to 8)
http://i.stack.imgur.com/foe7g.png[^]

This is the output I want to achieve: (no duplicate row being returned - Refer to Row 6 to 8)
http://i.stack.imgur.com/GkrHP.png[^]

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

Posted 7-Oct-15 23:16pm

Member 11999641

Updated 7-Oct-15 23:57pm

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ankur_Garg · Answer 1 · 2015-10-07T23:45:00

Solution 1

Seems the data should display as expected. Try to identify it by selecting the following query:

Select distinct DATE, STATUS, TITLE From TABLE

In the above case, the query should return 2 records for that row. If this isn't the case, please post the table values for both the rows and the table structure.

Posted 7-Oct-15 23:45pm

Ankur_Garg

Comments

Member 11999641 8-Oct-15 5:56am

hi Ankur_Garg, have tried to use the following query you provided but same rows are being returned. I tried to update my post. Please take a look.

Ankur_Garg 8-Oct-15 6:12am

Could you post your full SQL query?

waynetan123 · Answer 2 · 2015-10-07T23:52:00

SQL

;WTIH row_nums AS 
    (
        SELECT DATE, STATUS, TITLE, ROW_NUMBER() OVER (PARTITION BY DATE, STATUS, TITLE ORDER BY NUM ASC) AS Row_Num
        FROM (
             SELECT your required columns, COUNT(duplicated_rows_columnsname)
             FROM ***
             GROUP BY columnnames
             HAVING COUNT(duplicated_rows_columnsname) = 1
        )
    )
SELECT .................

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us