Select only the most recent record

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 'L

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

StackExchange Database Administrators Q#157324, answer score: 3