Removing Duplicates

Transact-SQL
1

Removing Duplicates

SELECT [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Posting Date], [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Document No_], [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Buy-from Vendor No_], [Pneumax India Pvt Ltd$Vendor].Name,
[Pneumax India Pvt Ltd$Purch_ Inv_ Line].No_, [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Item No_2], [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Direct Unit Cost], [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Unit of Measure],
[Pneumax India Pvt Ltd$Vendor].[Currency Code]
FROM [Pneumax India Pvt Ltd$Purch_ Inv_ Line] INNER JOIN
[Pneumax India Pvt Ltd$Vendor] ON [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Buy-from Vendor No_] = [Pneumax India Pvt Ltd$Vendor].No_
order BY [Pneumax India Pvt Ltd$Purch_ Inv_ Line].[Posting Date] desc

RESULT OF ABOVE QUERY

Document No_ Buy-from Vendor No_ No_ Unit of Measure Direct Unit Cost Posting Date Item No_2 Currency Code
I/IMP/NOD1718/0082 V00807 TR-001776 Meter 0.136 15-09-2017 0:00 TPU0640 USD
I/IMPFRT/NOD1718/078 V00900 FREIGHT 22783 15-09-2017 0:00
I/IMPFRT/NOD1718/078 V00900 FREIGHT 2451 01-08-2017 0:00
I/IMPCLR/NOD1718/076 V00026 CLEARING 3351 15-09-2017 0:00
I/IMPCLR/NOD1718/077 V00026 CLEARING 3181 02-07-2017 0:00

I need latest direct unit cost from each vendor in above list and remove rest of the entries for that particular vendor. Accordingly the outcome of the query should be as follows:-

Desired Output

Document No_ Buy-from Vendor No_ No_ Unit of Measure Direct Unit Cost Posting Date Item No_2 Currency Code
I/IMP/NOD1718/0082 V00807 TR-001776 Meter 0.136 15-09-2017 0:00 TPU0640 USD
I/IMPFRT/NOD1718/078 V00900 FREIGHT 22783 15-09-2017 0:00
I/IMPCLR/NOD1718/076 V00026 CLEARING 3351 15-09-2017 0:00

Could u please help me to design the above query to achieve above result?

2

To make it much easier for people trying to help, it is good practice to provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide

Also, when posting the above, using the formatting facilities removes any doubt of which data is which field (plus it's much easier for us and you to read):

  • ``` your text ```
  • <pre> your text </pre>

Now, seeing as you didn't provide table definitions and sample data and didn't use formatting facilities, this query might or might not be what you're looking for - in other words, I'm totally guessing:

select top(1) with ties
       i.[Posting Date]
      ,i.[Document No_]
      ,i.[Buy-from Vendor No_]
      ,v.Name
      ,i.No_
      ,i.[Item No_2]
      ,i.[Direct Unit Cost]
      ,i.[Unit of Measure]
      ,v.[Currency Code]
  from [Pneumax India Pvt Ltd$Purch_ Inv_ Line] as i
       inner join [Pneumax India Pvt Ltd$Vendor] as v
               on v.No_=i.[Buy-from Vendor No_]
  order by row_number() over(partition by i.[Buy-from Vendor No_]
                                 order by i.[Posting Date] desc
                            )
;
3

Posting Date Document No_ Buy-from Vendor No_ Name No_ No_ 2 Direct Unit Cost
9-Jun-17 I/IMP/NOD1718/0038 V00872 NINGBO INTELL PNEUMATIC TECHNOLOGY CO.,LTD TR-004233 PPCG8-02G 0.155
27-May-17 I/IMP/NOD1718/0030 V00872 NINGBO INTELL PNEUMATIC TECHNOLOGY CO.,LTD TR-004233 PPCG8-02G 0.155
28-Jan-11 I/EXP-TOH/09-10/1205 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.144
4-Jan-13 I/IMP/NOD1213/0105 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
30-Jul-13 I/IMP/NOD1314/0043 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
24-Jan-14 I/IMP/NOD1314/0112 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
3-Jul-14 I/IMP/NOD1415/0048 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
7-Feb-15 I/IMP/NOD1415/0161 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
29-Apr-15 I/IMP/NOD1516/0015 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
31-Aug-15 I/IMP/NOD1516/0071 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
4-Apr-16 I/IMP/NOD1617/0001 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
29-Nov-16 I/IMP/NOD1617/0116 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
30-Jul-12 I/IMP/NOD1213/0044 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
18-Feb-13 I/IMP/NOD1213/0116 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
28-Sep-13 I/IMP/NOD1314/0068 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
20-Feb-14 I/IMP/NOD1314/0123 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
26-Aug-14 I/IMP/NOD1415/0074 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
20-Feb-15 I/IMP/NOD1415/0168 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
29-May-15 I/IMP/NOD1516/0032 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
31-Oct-15 I/IMP/NOD1516/0101 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
19-May-16 I/IMP/NOD1617/0024 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
31-Jan-17 I/IMP/NOD1617/0149 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
18-Nov-10 I/TRD/09-10/0133 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.144
30-Jul-12 I/IMP/NOD1213/0045 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
8-May-13 I/IMP/NOD1314/0013 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
28-Sep-13 I/IMP/NOD1314/0068 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
2-May-14 I/IMP/NOD1415/0015 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
11-Oct-14 I/IMP/NOD1415/0095 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
21-Mar-15 I/IMP/NOD1415/0187 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
29-May-15 I/IMP/NOD1516/0032 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
1-Dec-15 I/IMP/NOD1516/0116 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
25-Jun-16 I/IMP/NOD1617/0044 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
21-Mar-17 I/IMP/NOD1617/0175 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
18-Nov-10 I/TRD/09-10/0134 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.144
6-Apr-11 I/EXP-OTH/11-12/0053 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.144
7-Aug-12 I/IMP/NOD1213/0050 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
30-Jul-13 I/IMP/NOD1314/0043 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
13-Nov-13 I/IMP/NOD1314/0082 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
3-Jul-14 I/IMP/NOD1415/0048 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
24-Dec-14 I/IMP/NOD1415/0135 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
24-Apr-15 I/IMP/NOD1516/0014 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
4-Jul-15 I/IMP/NOD1516/0047 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
16-Jan-16 I/IMP/NOD1516/0138 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155
7-Nov-16 I/IMP/NOD1617/0108 V00115 NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) TR-004233 PPCG8-02G 0.155

CREATE TABLE mytable (
Posting_Date VARCHAR(9) NOT NULL PRIMARY KEY
,Document_No_ VARCHAR(20) NOT NULL
,Buyfrom_Vendor_No_ VARCHAR(6) NOT NULL
,NAME VARCHAR(42) NOT NULL
,No_ VARCHAR(9) NOT NULL
,No_2 VARCHAR(9) NOT NULL
,Direct_Unit_Cost NUMERIC(5, 3) NOT NULL
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'9-Jun-17'
,'I/IMP/NOD1718/0038'
,'V00872'
,'NINGBO INTELL PNEUMATIC TECHNOLOGY CO.,LTD'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'27-May-17'
,'I/IMP/NOD1718/0030'
,'V00872'
,'NINGBO INTELL PNEUMATIC TECHNOLOGY CO.,LTD'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'28-Jan-11'
,'I/EXP-TOH/09-10/1205'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.144
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'4-Jan-13'
,'I/IMP/NOD1213/0105'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'30-Jul-13'
,'I/IMP/NOD1314/0043'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'24-Jan-14'
,'I/IMP/NOD1314/0112'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'3-Jul-14'
,'I/IMP/NOD1415/0048'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'7-Feb-15'
,'I/IMP/NOD1415/0161'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'29-Apr-15'
,'I/IMP/NOD1516/0015'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'31-Aug-15'
,'I/IMP/NOD1516/0071'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'4-Apr-16'
,'I/IMP/NOD1617/0001'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'29-Nov-16'
,'I/IMP/NOD1617/0116'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'30-Jul-12'
,'I/IMP/NOD1213/0044'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'18-Feb-13'
,'I/IMP/NOD1213/0116'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'28-Sep-13'
,'I/IMP/NOD1314/0068'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'20-Feb-14'
,'I/IMP/NOD1314/0123'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'26-Aug-14'
,'I/IMP/NOD1415/0074'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'20-Feb-15'
,'I/IMP/NOD1415/0168'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'29-May-15'
,'I/IMP/NOD1516/0032'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'31-Oct-15'
,'I/IMP/NOD1516/0101'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'19-May-16'
,'I/IMP/NOD1617/0024'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'31-Jan-17'
,'I/IMP/NOD1617/0149'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'18-Nov-10'
,'I/TRD/09-10/0133'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.144
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'30-Jul-12'
,'I/IMP/NOD1213/0045'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'8-May-13'
,'I/IMP/NOD1314/0013'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'28-Sep-13'
,'I/IMP/NOD1314/0068'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'2-May-14'
,'I/IMP/NOD1415/0015'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'11-Oct-14'
,'I/IMP/NOD1415/0095'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'21-Mar-15'
,'I/IMP/NOD1415/0187'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'29-May-15'
,'I/IMP/NOD1516/0032'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'1-Dec-15'
,'I/IMP/NOD1516/0116'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'25-Jun-16'
,'I/IMP/NOD1617/0044'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'21-Mar-17'
,'I/IMP/NOD1617/0175'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'18-Nov-10'
,'I/TRD/09-10/0134'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.144
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'6-Apr-11'
,'I/EXP-OTH/11-12/0053'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.144
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'7-Aug-12'
,'I/IMP/NOD1213/0050'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'30-Jul-13'
,'I/IMP/NOD1314/0043'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'13-Nov-13'
,'I/IMP/NOD1314/0082'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'3-Jul-14'
,'I/IMP/NOD1415/0048'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'24-Dec-14'
,'I/IMP/NOD1415/0135'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'24-Apr-15'
,'I/IMP/NOD1516/0014'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'4-Jul-15'
,'I/IMP/NOD1516/0047'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'16-Jan-16'
,'I/IMP/NOD1516/0138'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

INSERT INTO mytable (
Posting_Date
,Document_No_
,Buyfrom_Vendor_No_
,NAME
,No_
,No_2
,Direct_Unit_Cost
)
VALUES (
'7-Nov-16'
,'I/IMP/NOD1617/0108'
,'V00115'
,'NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT)'
,'TR-004233'
,'PPCG8-02G'
,0.155
);

Data to be sorted as per posting date (Latest date on top – descending order)
The query should extract only one record of latest posting date from duplicate records where Buy-from Vendor No_ & No_ 2
are repeating.

Desired output
+--------------+--------------------+---------------------+--------------------------------------------+-----------+-----------+------------------+
| Posting Date | Document No_ | Buy-from Vendor No_ | Name | No_ | No_ 2 | Direct Unit Cost |
+--------------+--------------------+---------------------+--------------------------------------------+-----------+-----------+------------------+
| 9-Jun-17 | I/IMP/NOD1718/0038 | V00872 | NINGBO INTELL PNEUMATIC TECHNOLOGY CO.,LTD | TR-004233 | PPCG8-02G | 0.155 |
| 21-Mar-17 | I/IMP/NOD1617/0175 | V00115 | NINGBO ISAIAH PNEUMATICS CO LTD.(IMPORT) | TR-004233 | PPCG8-02G | 0.155 |
+--------------+--------------------+---------------------+--------------------------------------------+-----------+-----------+------------------+

4

Change this

  order by row_number() over(partition by i.[Buy-from Vendor No_]
                                 order by i.[Posting Date] desc
                            )

to

  order by row_number() over(partition by i.[Buy-from Vendor No_]
                                 order by cast(i.[Posting Date] as date) desc
                            )```
5

Thanks for your immediate reply but this is also not working. when I apply the filter on No_2 both the below statements are giving the same result but

order by row_number() over(partition by i.[Buy-from Vendor No_]
order by i.[Posting Date] desc
)

order by row_number() over(partition by i.[Buy-from Vendor No_]
order by cast(i.[Posting Date] as date) desc
)

when i don't apply filter on No_2 it is not picking all the records as per my specified criteria (only those records with latest posting dates should be picked where Buy-from Vendor No_ & No_ 2 are matching in each record like suppose there are 10 records and in each record Buy-from Vendor No_ & No_ 2 are matching then it should pick only the record with latest posting date). It is happening only in case when i apply filter On No_2 for particular item but without filter it is not picking all the unique records.

Pl. advice.

6

by using following statements

order by row_number() over(partition by i.[Buy-from Vendor No_]
order by i.[Posting Date] desc
)

order by row_number() over(partition by i.[Buy-from Vendor No_]
order by cast(i.[Posting Date] as date) desc
)

I get exactly the same records i.e. 661 in both the above cases but when i have exported the table to excel and removed duplicated using excel function I get 7188 record after removal of duplicates rows/records.

7

Your sample data has duplicate primary key and Unit_of_measure and Currency Code fields are missing and second table holding the vendor name are missing. Removing the primary key and implementing my suggestion, to my original solution (commenting out the second table and missing fields), gives me:

Posting_Date  Document_No_        Buyfrom_Vendor_No_  No_        No_2       Direct_Unit_Cost
21-Mar-17     I/IMP/NOD1617/0175  V00115              TR-004233  PPCG8-02G  0.155
9-Jun-17      I/IMP/NOD1718/0038  V00872              TR-004233  PPCG8-02G  0.155

The complete Query I used was:

select top(1) with ties
       i.[Posting_Date]
      ,i.[Document_No_]
      ,i.[Buyfrom_Vendor_No_]
/*
      ,v.Name
*/
      ,i.No_
      ,i.[No_2]
      ,i.[Direct_Unit_Cost]
/*
      ,i.[Unit_of_Measure]
      ,v.[Currency Code]
*/
  from mytable as i
/*
  from [Pneumax India Pvt Ltd$Purch_ Inv_ Line] as i
       inner join [Pneumax India Pvt Ltd$Vendor] as v
               on v.No_=i.[Buy-from Vendor No_]
*/
  order by row_number() over(partition by i.[Buyfrom_Vendor_No_]
                                 order by cast(i.[Posting_Date] as date) desc
                            )
;
8

Thanks Bitsmed. I have achieved the desired results with a slight addition (i.No_) in your answer as

order by row_number() over(partition by i.[Buy-from Vendor No_],i.No_
order by i.[Posting Date] desc
).

Thanks for your assistance once again.

Manish