Getting duplicate record from sql query

See more:

VB

WPF

SQL-Server-2008R2

Hi all

Below is my query

SELECT ROW_NUMBER() OVER (ORDER BY om.OrderFormNo) AS SI ,
       om.OrderFormNo,
       om.OrderDate,
       ag.CompanyName AS Agency,
       sup.Name AS Supplier,
       dis.Name AS Distributor,
       it.ItemName,
       od.Quantity,
       od.Rate,
       odc.ReceivedQty,
       odc.ReceivedDate,
       odca.CancelQty,
       odca.CancelDate,
       od.Quantity-(odc.ReceivedQty+odca.CancelQty) AS Balance
FROM OrderMaster om
JOIN OrderDetails od ON om.OMID=od.OMIDFormRef
JOIN OrderDetailsChild odc ON od.ODID=odc.OrderDetailsIDRef
JOIN OrderDetailsCancel odca ON od.ODID=odca.OrderDetailsIDRef
JOIN ItemInfo it ON od.IIIDItemRef=it.IIID
JOIN Agency ag ON om.AIDAgencyRef=ag.AID
JOIN SupplierMainGroup sup ON om.SMGIDRef=sup.SMGID
JOIN MainDistributor dis ON om.MDIDRef=dis.MDID
WHERE sup.Name='trtrttrt'

and the output i am getting is


SI OrderFormNo OrderDate Agency Supplier Distributor ItemName Quantity Rate ReceivedQty ReceivedDate CancelQty CancelDate Balance


1 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 4 2013-11-21 3 2013-11-21 18

2 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 5 2013-11-21 3 2013-11-21 17

3 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 4 2013-11-21 3 2013-11-21 18

4 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 5 2013-11-21 3 2013-11-21 17

Now the problem is actually i am having only 2 rows i.e. SI. 1 and 2
but here it's duplicating and displaying same rows again.

Please tell me what i have to modify in the query.

Modify: I use Distinct and it's working fine only if i remove this line ROW_NUMBER() OVER (ORDER BY om.OrderFormNo) AS SI, else it's not working.
so can anyone tell me the alternate for ROW_NUMBER() OVER (ORDER BY om.OrderFormNo) AS SI line.