SQL - Returning only the most recent row

Question

I'm working on aging report for accounts over 30 days and I am joining the "Comment" table because AP wants to see the most recent comment if it exists for a customer.

The comment table can have multiple rows for the same customer for each comment that is attached to that customer with the first record being the oldest comment.

When I join the tables everything works, but it returns the first comment not the latest.

How can I get it to to look for multiple rows for the same customer and then return the most recent comment?

*Note - The comment table does not have dates just a field the starts at 1000 and increases for each new comment row

This is what i have right now:

SELECT        
    dbo.[Pioneer-CO$Purchase Header].No_, 
    dbo.[Pioneer-CO$Purchase Line].[Amt_ Rcd_ Not Invoiced], 
    dbo.[Pioneer-CO$Purch_ Rcpt_ Header].[Posting Date], 
    dbo.[Pioneer-CO$Comment Line].[Comment], 
    dbo.[Pioneer-CO$Purchase Header].[Sell-to Customer No_]
FROM
    dbo.[Pioneer-CO$Purchase Header] 
INNER JOIN
    dbo.[Pioneer-CO$Purchase Line] ON dbo.[Pioneer-CO$Purchase Header].No_ = dbo.[Pioneer-CO$Purchase Line].[Document No_] 
INNER JOIN
    dbo.[Pioneer-CO$Purch_ Rcpt_ Header] ON dbo.[Pioneer-CO$Purchase Header].No_ = dbo.[Pioneer-CO$Purch_ Rcpt_ Header].[Order No_] 
INNER JOIN
    dbo.[Pioneer-CO$Comment Line] ON dbo.[Pioneer-CO$Purchase Header].[Sell-to Customer No_] = dbo.[Pioneer-CO$Comment Line].[No_]
WHERE
    (dbo.[Pioneer-CO$Purch_ Rcpt_ Header].[Posting Date] < DATEADD(day, - 30, GETDATE()))
GROUP BY 
    dbo.[Pioneer-CO$Purchase Header].No_, 
    dbo.[Pioneer-CO$Purchase Line].[Amt_ Rcd_ Not Invoiced], 
    dbo.[Pioneer-CO$Purch_ Rcpt_ Header].[Posting Date], 
    dbo.[Pioneer-CO$Comment Line].[Comment], 
    dbo.[Pioneer-CO$Purchase Header].[Sell-to Customer No_]
HAVING        
    (dbo.[Pioneer-CO$Purchase Line].[Amt_ Rcd_ Not Invoiced] > '0')
ORDER BY 
    dbo.[Pioneer-CO$Purch_ Rcpt_ Header].[Posting Date] DESC

As well as your described problem, bear in mind that as you're using only INNER JOINs, if a customer has no comments at all, they won't appear on the report, no matter how old their debt. — Matt Gibson, Oct 19 '16 at 21:37
`[posting date] desc` will return the first/latest one, since descending means "newest first". try `asc` instead - oldest first — Marc B, Oct 19 '16 at 21:37

bib1257 · Answer 1 · 2016-10-19T22:08:32.577

1

I would use something like this

select customer.*, lastComment.*
from account
cross apply (select top 1 * from comment where
    comment.customerId=customer.customerId order by commentnumber desc) lastComment
where customer.age>30

If you need to include customers without comments use outer apply instead of cross apply

edited Oct 19 '16 at 22:08

answered Oct 19 '16 at 21:41

bib1257

325
2
7

score 1 · Answer 2 · answered Oct 19 '16 at 23:39

I would use following query:

OUTER APPLY
(select top 1 [Comment] from dbo.[Pioneer-CO$Comment Line] where dbo.[Pioneer-CO$Purchase Header].[Sell-to Customer No_] = dbo.[Pioneer-CO$Comment Line].[No_] order by [Posting Date] desc) Comments

Also, use Comments.[Comment] in the SELECT statement instead of dbo.[Pioneer-CO$Comment Line].[Comment]

The dbo.[Pioneer-CO$Comment Line] table should be OUTER APPLY join in the query. Also, order by comments posting date or incremental ID.

score 0 · Answer 3 · answered Oct 19 '16 at 21:40

0

You should look at the ID or primary key, I'd imagine the newer comments have a higher ID. You could join the tables and do a Max(ID) or look for the latest timestamp if you use that on your table.

answered Oct 19 '16 at 21:40

Dave B

659
8
29

I'm pretty new to SQL and I was having trouble using the MAX function. Any suggestions on how that code would look? – Rkindred Oct 19 '16 at 21:43
@Rkindred lookup [MAX - MSDN](https://msdn.microsoft.com/en-us/library/ms187751.aspx). Microsoft's MSDN is a great resource on syntax and official uses of SQL keywords. Also, understand that unless the function is by itself, you MUST have a column (non-aggregate) that appears in the `SELECT`, also appear in the `GROUP BY` clause. For an overview with links to other aggregates, see [AGGREGATE FUNCTIONS - MSDN](https://MSDN.Microsoft.com/en-us/library/ms173454.aspx) – clifton_h Oct 20 '16 at 00:43

Udaan · Answer 4 · 2016-10-19T22:35:35.060

0

Looks like you have Customers to Comments relationship is 1 to Many(0). How about trying something like this,

SELECT TOP 1 Customers.*, Comments.*
FROM Customers
JOIN Customers ON Customers.Id = Comments.Id
ORDER BY Comments.Id DESC

Following Added after Bib's comments

You can also try following

SELECT Customers.*, Comments.*
FROM Comments
JOIN
(SELECT MAX(Id) As CId FROM Comments GROUP BY CustomerId) AS CommentsMAX
ON CommentsMAX.CId = Comments.ID
JOIN 
Customers ON Customers.Id = Comments.Id

edited Oct 19 '16 at 22:35

answered Oct 19 '16 at 22:02

Udaan

87
1
2
14

This will only return one row, It will be the the latest comment and the customer that this comment is linked to – bib1257 Oct 19 '16 at 22:06

SQL - Returning only the most recent row

4 Answers4

Linked