Last entry in SQL group

Question

I have a table in which we can have the same ticket no.for multiple entries.i I want to get the last ticket number (of the same number) based on Initiated on column.

Ticket VendorTicket InitiatedOn Comments 198165 test 2012-08-10 16:31:33.707 test 199485 sagar 2012-08-11 12:36:25.720 sagar 199485 sagar1 2012-08-11 14:36:25.720 sagar1 199478 kishor 2012-08-11 12:37:21.923 kishor 

I wrote this query in T-SQL in a stored procedure. It gives me the error message xObj = {"Incorrect syntax near '.'."}

My T-SQL

 `@SQL='Select ''<a href="javascript:editTicketByIDAction('' + CONVERT(VarChar(Max), Ticket) + '')">'' + CONVERT(VarChar(Max),Ticket) + ''</a>'' t1.Ticket,t1.VendorTicket[Vendor Ticket],t1.Comments From VendorTickets t1 WHERE NotifyOn <= GetDate() And NotifyOn Is Not Null AND NOT EXISTS (SELECT * FROM VendorTickets t2 WHERE t1.Ticket = t2.Ticket AND t1.InitiatedOn < t2.InitiatedOn)'` 

Answer 1

General solution (works in any database) for this problem:

 SELECT * FROM [my_table] t1 WHERE NOT EXISTS ( SELECT * FROM [my_table] t2 WHERE t1.Ticket = t2.Ticket AND t1.InitiatedOn < t2.InitiatedOn ) 

This reads: get the whole value from my_table, where there is no ticket with the same Ticket ID and later date.

Notes:

• This can return duplicates if there are two “most recent” tickets with the same InitiatedOn value.
• Must have appropriate indexes for Ticket and InitiatedOn columns

Answer 2

try the following:

This will give the most recent entries from each group.

 select * from <table> t join (select Ticket,max(InitiatedOn) as InitiatedOn from <table> group by Ticket)a on t.Ticket=a.Ticket and t.InitiatedOn=a.InitiatedOn 

Answer 3

 SELECT Ticket, VendorTicket, InitiatedOn, Comments FROM ( SELECT Ticket, VendorTicket, InitiatedOn, Comments, ROW_NUMBER() OVER (PARTITION BY Ticket ORDER BY InitiatedOn DESC) AS Row FROM Table ) AS Q WHERE Q.Row = 1 

Answer 4

If row_number() over() is available to you.

 select T.Ticket, T.VendorTicket, T.InitiatedOn, T.Comments from ( select Ticket, VendorTicket, InitiatedOn, Comments, row_number() over(partition by Ticket order by InitiatedOn desc) as rn from YourTable ) T where T.rn = 1 

Answer 5

 Select t1.* from tickets t1 left join tickets t2 on (t1.ticket = t2.ticket and t2.initiatedon > t1.initiatedon) where t2.ticket is null; 

Answer 6

 select * from table where ticket=199485 order by InitiatedOn desc limit 1;