I've got a problem with creating a SQL query in order to get only the most recent record from multiple records. I've already checked different solutions like a subselect; unfortunately that does not work for me.
The query I've created so far puts out about 1300 records. What I need is only the newest one (by wfi.CREATEDDATETIME
) of every InvoiceNumber
. Here's the example data for ONE InvoiceNumber
:
Company | UID | Approver | Due | InvoiceDate | InvoiceNumber | Vendor | Created S0009 | USR1 | Random Guy | ... | 20.10.2016 | 123456 | Random Vendor1 | 2016-10-30 10:00:22.000 S0009 | USR1 | Random Guy | ... | 20.10.2016 | 123456 | Random Vendor1 | 2016-10-30 10:00:22.000 S0009 | USR2 | Some Guy | ... | 01.01.2000 | 123456 | Random Vendor1 | 2016-11-01 12:30:16.000 S0009 | USR2 | Some Guy | ... | 01.01.2000 | 123456 | Random Vendor1 | 2016-11-01 12:30:16.000
The result I need is:
Company | UID | Approver | Due | InvoiceDate | InvoiceNumber | Vendor | Created S0009 | USR2 | Some Guy | ... | 01.01.2000 | 123456 | Random Vendor1 | 2016-11-01 12:30:16.000
Query
SELECT
Upper(WFI.COMPANYID) as Company,
WFI.USERID as UID,
INF.NAME as Approver,
CONVERT(varchar(10),duedatetime,4) as Due,
right(left(Document,20),10) as InvoiceDate,
SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as InvoiceNumber,
Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
wfi.CREATEDDATETIME as Created
FROM
[TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID
WHERE
DATASOURCENAME Like 'Ledgerjourna%'
AND Datediff(day,Duedatetime,getdate())>3
AND WFS.DOCUMENTTYPE='Special'
ORDER BY
WFI.COMPANYID asc
These tables are standard workflow and employee tables from Dynamics AX 2012 on SQL Server Version 12.0.4100.
The createddatetime
could be the same for multiple records. In this case I would like to select just one of them, it doesn't matter which one.
Best Answer
This might work. Basically it's a ROW_NUMBER function that you will have to identify a key for (you Mentioned InvoiceNumber). Once you do, it will return an ordered value where all your "duplicates" will be 2+. Simply adding a where clause where ROWNUM =1 should get you the first record (ordered by the CreatedDate).
Feel free to comment out the
WHERE main.ROWNUM=1
clause so that you can see your Rownum in action.If you don't want your
ROWNUM
column to show in your final result set, then just replace the firstSELECT *
with the actual columns you want to select (using their aliases).