Select only the most recent record

Question

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.

Answer 1

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).

SELECT main.* FROM
(
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,
--Added RowNumber Function Below
ROW_NUMBER() OVER (PARTITION BY **InsertYourKeyToAUniqueRecordHere** ORDER BY wfi.CREATEDDATETIME DESC) AS ROWNUM

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'

)main

WHERE main.ROWNUM =1 --Add this clause to only return the first record
ORDER BY main.Company asc

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 first SELECT * with the actual columns you want to select (using their aliases).