row number() function in sql server.

Question

0.00/5 (No votes)

See more:

Hello,

SQL

AS
BEGIN
WITH cte
  AS
  (
    SELECT row_number () OVER ( PARTITION BY
    BillofMaterialsId, MasterItemId, MemberItemId, MemberRevisionId  ORDER BY BillofMaterialsId DESC ) AS ROW,
    *
   FROM BOMDetails_Stg
  )
  DELETE CTE
  WHERE ROW > 1

What this code does actually please explain me.

Thanks in advance.

Posted 4-Jan-13 0:38am

bapu_reddy

Add a Solution

Comments

__TR__ 4-Jan-13 6:44am

The sql statement is deleting duplicate rows from BOMDetails_Stg which have same BillofMaterialsId, MasterItemId, MemberItemId and MemberRevisionId

bapu_reddy 4-Jan-13 6:54am

Thanks bro, but why CTE is used? Please explain me.

Amir Mahfoozi 4-Jan-13 7:01am

In my opinion it was just an option. If you want to use row_number result for further operations it is necessary to use "temporary tables" or "derived tables" or "multiple CTEs" in one query statement.

__TR__ 4-Jan-13 7:22am

As Amir said it is just an option. By using CTE you can avoid creating a temp table and if you delete a row in CTE the row gets deleted from table BOMDetails_Stg as well. You can also do it using temp tables but you will need to declare the table first, insert data into it and then delete the rows from BOMDetails_Stg using data in temp table.

1 solution

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)

RDBurmon · Answer 1 · 2013-01-15T18:16:00

Below is the explanation

SQL

WITH cte
  AS
  (
    SELECT row_number () OVER ( PARTITION BY
    BillofMaterialsId, MasterItemId, MemberItemId, MemberRevisionId  ORDER BY BillofMaterialsId DESC ) AS ROW,
    *
   FROM BOMDetails_Stg
  )

Above code will rank duplicate values . It means if you have below data
BillofMaterialsId MasterItemId MemberItemId MemberRevisionId
AAA BBB CCC DDD
AAA BBB CCC DDD
EEE FFF GGG HHH

Then row_number () function will rank first two rows as 1 , 2 and third row as 1.
So if we want to remove duplicate from above table then we need to delete data which has rownumber as 2

SQL

DELETE CTE
 WHERE ROW > 1

If the first code WITH function will create CTE table with ROW_NUMBER as mentioned earlier . This query will remove data from actual table where ROW_NUMBER is greater than 1. Means it will remove duplicate row.