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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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