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

sql-server-2008 no-duplicates partition-by
Felicia Soh picture Felicia Soh · Oct 8, 2015 · Viewed 143.7k times · Source

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

Answer

Ankit Raonka picture Ankit Raonka · Apr 13, 2018

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More