How do I get the max date without listing all of the columns in the select?

Question

I wrote a query to bring back all support tickets, but the rows for RequestID are repeating, I'd like to get the max from the created date column.

Example of the repeating RequestID:

Query being used:

select * 
from [CMSystemScripts].[dbo].[CMS_CMS_and_MandR_Ticketing] Tick
inner join [dbo].[CMS_CMS_and_MandR_Ticketing_Analysis] analysis on tick.requestid = analysis.RequestID
order by 1

I thought that maybe putting MAX(*) would return the max for all columns, but I get an error

Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '*'

Do I have to list the columns in the select to get the max or is there another way to accomplish this?

I am expecting to see one record for each RequestID which contains the max date on the created date column.

Any assistance would be greatly appreciated.

Thank you!

Answer 1

You could use the "order by" and "group by" operators to define the ordering and grouping of lines: you could follow an example like this:

SELECT tick.requestId, analysis.[yourDateCol]
FROM ...
INNER ...
GROUP BY analysis.[yourDateCol]
ORDER BY analysis.[yourDateCol] desc

Answer 2

Maybe you could try:

  WITH RankedTickets AS (
    SELECT 
        Tick.RequestID,
        Tick.OtherColumn1, -- Replace OtherColumn1 with the actual column names from the Tick table
        analysis.OtherColumn2, -- Replace OtherColumn2 with the actual column names from the analysis table
        analysis.CreatedDate,
        ROW_NUMBER() OVER (PARTITION BY Tick.RequestID ORDER BY analysis.CreatedDate DESC) AS rn
    FROM 
        [CMSystemScripts].[dbo].[CMS_CMS_and_MandR_Ticketing] Tick
        INNER JOIN [dbo].[CMS_CMS_and_MandR_Ticketing_Analysis] analysis ON tick.requestedid = analysis.RequestID
)
SELECT 
    RequestID,
    OtherColumn1, -- Replace OtherColumn1 with the actual column names from the Tick table
    OtherColumn2, -- Replace OtherColumn2 with the actual column names from the analysis table
    CreatedDate
FROM RankedTickets
WHERE rn = 1
ORDER BY 1;

This query is a common table expression (CTE) to first assign a row number to each row within the partiotion of rows with the same RequestId, orderd by CreatedDate in descending order. THen, it selects only the rows where the row number is 1. Which is the maximum for CreatedDate for each RequestID