CODEWITHSUNDEEP
sql-serverdatabase
Gholamreza Asadi
Gholamreza Asadi

Reputation: 105

How to return latest record from SQL Server

I have this query:

SELECT        
    t_ticket.ticketID, t_ticket.addedDate, t_ticket.question, 
    t_ticket.code, t_ticket.priority, t_orgSection.title, 
    t_actionTicket.addedDateAction, t_actionTicket.title AS Expr1 
FROM            
    t_actionTicket 
INNER JOIN
    t_ticket ON t_actionTicket.ticketID_FK = t_ticket.ticketID 
INNER JOIN
    t_orgSection ON t_ticket.orgSectionID_FK = t_orgSection.orgSectionID 
WHERE        
    (t_ticket.userID_FK = @userid) AND (t_ticket.cusDelete = 0)

I want to return just the latest record in t_actionTicket table for each row in t_ticket table.

Upvotes: 0

Views: 47

Answers (3)

Gholamreza Asadi
Gholamreza Asadi

Reputation: 105

I find this solution

SELECT  t_ticket.ticketID, t_ticket.addedDate, t_ticket.question,
     t_ticket.code, t_ticket.priority, t_actionTicket.addedDateAction, 
    t_actionTicket.title AS Expr1        
    FROM  t_actionTicket INNER JOIN t_ticket ON 
    t_actionTicket.ticketID_FK =   t_ticket.ticketID INNER JOIN
                                 (SELECT        ticketID_FK, MAX(addedDateAction) AS maxDate
                            FROM            t_actionTicket AS t_actionTicket_1
                            GROUP BY ticketID_FK) AS b ON t_actionTicket.ticketID_FK = b.ticketID_FK AND t_actionTicket.addedDateAction = b.maxDate         
WHERE        (t_ticket.userID_FK = @userid) AND (t_ticket.cusDelete = 0)

Upvotes: 0

Adnan Nazir
Adnan Nazir

Reputation: 86

You can try this one too, just alternative:

    SELECT t_ticket.ticketID, t_ticket.addedDate, t_ticket.question,t_ticket.code, t_ticket.priority, t_orgSection.title,t_actionTicket.addedDateAction, t_actionTicket.title AS Expr1
    FROM t_ticket INNER JOIN 
    (select TOP 1 t_actionTicket.* from t_actionTicket INNER JOIN
                            t_ticket on t_ticket.ticketID = t_actionTicket.ticketID_FK ORDER BY 
                            t_actionTicket.addedDateAction DESC ) AS t_actionTicket
             ON t_actionTicket.ticketID_FK = t_ticket.ticketID 
        INNER JOIN t_orgSection ON t_ticket.orgSectionID_FK = t_orgSection.orgSectionID
        WHERE        (t_ticket.userID_FK = @userid) AND (t_ticket.cusDelete = 0)

I assume that addedDateAction uses timestamp when new record is inserted.

Upvotes: 0

APH
APH

Reputation: 4154

You can use row_number to pick just the top row for each ticket ID, by ordering by your date and then making row = 1 one of your join criteria. See as follows:

Replace 

FROM t_actionTicket INNER JOIN
t_ticket ON t_actionTicket.ticketID_FK = t_ticket.ticketID

with

FROM   
    (select *
        , row_number() over (partition by ticketID_FK order by TicketActionDate desc) as RN
    from t_actionTicket ) at
INNER JOIN t_ticket 
ON at.ticketID_FK = t_ticket.ticketID and at.RN = 1

I guessed at the name of your date column in ActionTicket, so you will probably need to change that.

Upvotes: 1

Related Questions