ssis sort result and row_numer in sql server result not match

Question

Hi I have one doubt in ssis
delete duplicate records using sort transformation and using row_number function in sql server
records not matching exactely.
how will match exately in ssis sort and sql server row_number .
source data :
CREATE TABLE [dbo].emp
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (1, N'a', 100)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (2, N'b', 200)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (3, N'a', 100)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (4, N'b', 200)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (5, N'a', 100)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (6, N'd', 200)

in ssis:

sql server :
select * from (
SELECT * ,row_number()over (partition by name,sal order by name asc ,sal asc ) rn
FROM [test123].[dbo].[emp]
)a where rn=1

here both result (ssis sort and sql server row number) not matching.
please tell me .how will achieve to match both result .

Answer 1

Hi @harinathu ,

Please check with below code.

select * from (  
SELECT *,row_number()over (partition by name,sal order by name asc ,sal asc,id desc ) rn  
FROM [dbo].[emp]  
)a where rn=1  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

records not matching exactely.

Match in which way?

delete duplicate records

Duplicate mean they are the same, so why does any order matter?