How can I select the most recent item from a column in a report with several columns in SQL?

Question

I have a report with several columns, one of those being 'comments'. I am needing to show only the most recent comment out of all under that client. I do have a date field of when the comment was created as well. How can this best be accomplished?

Answer 1

select TOP 1 * from report order by comment_time desc

Where "report" is the name of your table and "comment_time" is the time the comment was created.

Select TOP 1 will eliminate all the other rows and will return only the first row (which would be the most recent comment of yours)

It would be better to change the the data type of your "comment_time" to datetime for improved results. Only using date will not be able to sort those comments which are created on the same day

Answer 2

There are a few ways you can do this but my choice would be to add a column to your dataset query that gives you the comment you need on every row. Then in the report we just hide all but the first copy of it.

Working thru a simple example. We have two clients, 'Dave' and 'Bob' with some numbers and dates against then. Each record also has a comment.

We can use a subquery to get the last comment for each client and append it to the other columns like this....

here I have just mocked up some data in table @t and then queried it to get the desired result.

DECLARE @t TABLE (client varchar(20), iVal int, sComment varchar(20), dtEntrySeq datetime)

INSERT INTO @t VALUES 
('Dave', 5, '1st Dave comment',    '2021-01-15 09:00:00'),
('Dave', 15, '2nd Dave comment',   '2021-01-29 09:00:00'),
('Dave', 25, '3rd Dave comment',   '2021-01-29 09:30:00'),
('Bob', 35, '1st Bob comment', '2021-01-01 09:00:00'),
('Bob', 45, '2nd Bob comment', '2021-01-02 09:00:00'),
('Bob', 55, '3rd Bob comment', '2021-01-03 09:00:00'),
('Bob', 65, '4th Bob comment', '2021-01-04 09:00:00'),
('Bob', 75, '5th Bob comment', '2021-01-06 09:00:00')

SELECT 
    a.*, lc.sComment as LastComment
    FROM @t a 
    JOIN (
            SELECT * FROM 
                (SELECT *, ROW_NUMBER() OVER(PARTITION BY client ORDER BY dtEntrySeq desc) as RowN FROM @t) r
                WHERE r.RowN = 1
        ) lc on a.client = lc.client

This gives us the following output...

We don't need the date and original comment column really but I left them in for clarity.

Now to the Report.

Create a new report, use the SQL above as the dataset query.

Next add a table and drag the required columns to the table control. Create a row group that groups by client.

Now click the textbox that contains the 'last comment' and in the properties window, change the HideDuplicates property to the name of the row group, in this example it will be called `client'. If you don't use the rowgroup here then, if Bob and Dave's last comments were identical, it would hide all comments except the first for the whole dataset rather than resetting on each client.

This is a quick GIF of the report being built and the final output to demonstrate.