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

User · Answer

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

User · Answer

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

User · Answer

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  lt column names gt       AS tbl

[sql-server-2008] How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned

Examples related to sql-server-2008

Examples related to no-duplicates

Examples related to partition-by