CODEWITHSUNDEEP
sql-serversql-server-2012
Mitesh Jain
Mitesh Jain

Reputation: 565

How to get rid of duplicate records

Hi I have created one below query and I am facing issue

SqlQuery

;with tbl as
(
select ROW_NUMBER() over(partition by oi.Name,T.gwtxid order by o.order_id desc) as number,
isnull(oi.Name,'') AS 'Name'
,isnull(T.gwtxid,'') as 'gwtxid'
FROM ORDERS o
INNER JOIN 
ORDERITEMS oi on o.Order_ID=oi.Order_ID
left JOIN 
PAYMENTS P on p.Order_ID=o.Order_ID    
left JOIN 
TRANSACTIONS T ON T.Transaction_ID=P.Transaction_ID    
WHERE O.TOTALCHARGES>0 and o.OrderNumber is not null and o.Order_ID=195180046

)
select * from tbl where  number=1

And My record seems to be as below Table structure

Number   Name           gwtxid  
1      Skunk2 Lower   00WA90298MF810153R
1      Skunk2 Lower   03GF53362DR764524D
1      CA_Taxable     3GF53362DR764524D
1      CA_Taxable     0WA90298MF810153R
1      UPS Ground      0WA90298MF810153R
1      UPS Ground      3GF53362DR764524D

I just need uniqueness for name and then transaction id can be repeated

Below is my requirement some thing as Required Table structure

Name              gwtxid  
Skunk2 Lower      03GF53362DR764524D
CA_Taxable        0WA90298MF810153R
UPS Ground

Edited Required Table structure where gwtxid is repeated can also be fine

Name              gwtxid  
Skunk2 Lower      03GF53362DR764524D
CA_Taxable        0WA90298MF810153R
UPS Ground        03GF53362DR764524D

Can any one help me out from this, I had tried using the rownumber() and it is giving wrong records

Upvotes: 0

Views: 49

Answers (1)

Alex
Alex

Reputation: 5157

From MSDN SELECT - OVER Clause

(PARTITION BY clause) Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

In you case: partition by oi.Name,T.gwtxid gives you these groups/partitions:

Group #
1      Skunk2 Lower   00WA90298MF810153R
2      Skunk2 Lower   03GF53362DR764524D
3      CA_Taxable     3GF53362DR764524D
4      CA_Taxable     0WA90298MF810153R
5      UPS Ground      0WA90298MF810153R
6      UPS Ground      3GF53362DR764524D

Since each group in your example only contains one row, you get number = 1 for every record.

If you change to partition by oi.Name then you get the following groups:

Group #
1      Skunk2 Lower   00WA90298MF810153R
1      Skunk2 Lower   03GF53362DR764524D
2      CA_Taxable     3GF53362DR764524D
2      CA_Taxable     0WA90298MF810153R
3      UPS Ground      0WA90298MF810153R
3      UPS Ground      3GF53362DR764524D

Then you should get the answer you are looking for.

Upvotes: 2

Related Questions