Hi,

I am going crazy with a problem and would really appreciate if anybody could help me with it!! Here is what I am trying to achieve:

I have two tables - "tickets" and "ticket_updates"

tickets has following columns:
TicketId, UserId, AssignedToId, ... DateCreated, Status

ticket_updates has following columns:
UpdateId, TicketId, UpdaterId, ... DateUpdated

I am trying to select the rows from the ticket_updates table that has the latest update [MAX(ticket_updates.DateUpdated)] for all the open tickets (tickets.status = 1)


Here is what I have:

SELECT ticket_updates.*,
MAX( ticket_updates.DateUpdated ) AS DateUpdated
FROM ticket_updates
LEFT JOIN tickets ON tickets.TicketId = ticket_updates.TicketId
WHERE tickets.status = 1
GROUP BY ticket_updates.TicketId

But obviously (as I figured out now) this doesn't give me the right rows.

I have tried do a sub select as in:

WHERE ...
ticket_updates.DateUpdated = (SELECT MAX( ticket_updates.DateUpdated )
FROM ticket_updates
GROUP BY ticketid)

but obviously (again) this doesn't help since the sub select fetches multiple rows. And if I exclude the GROUP BY then I would get only one row in the output which would have the max DateUpdated. I am looking to fetch the rows containing the last updates for all the tickets that are open (status = 1).

I have tried some others as well (including HAVING) but I am just unable to get there

Any help is really appreciated.

Thanks!
Syed

Thanks r937. That worked like a charm!

I prefer the subquery route you were after originally. As the nested expression will eventually just get too large to perform well. How about something like:
Dave

dav1mo,

Your query would mean that the sub query for this section

will have to be run per each rows of TCK.TICKETID. This will be very expensive on large data.

I wrote a blog entry about this: Microshell Optimizing SQL that selects the max/min/etc from a group.

r937 query would perform much better in large table.

Actually it would not perform worse. In the manner I introduced it, it only gets the max timestamp for each ticketid, that is satisfied by the rest of the where clause. Whereas, the way Rudy accomplished it was to get the max timestamp for every ticket in the table to store in the nested expression. I think with a test of small amount of data Rudy's example will perform faster and for a test with large amount of data mine will perform faster. as long as the proper indexing on the predicates of the where clause exist.
Dave

Mnirwan,
I just read the post you had supplied and yes in your case you are correct, but that implies that you are looking for a count/max/min of everything in the table everytime. In the OPs question they wanted only for tickets with a status of 1. I assume that this means the ticket is probably active and after some amount of time there will be many tickets whose status is no longer 1 and that an index on that column would be used by the optimizer to select tickets whose status is a 1. In a case of this sort it is much faster to do the max on just the tickets that you are interested in rather than on the millions you are not. It all depends on what you are after, which will be the faster.
Dave

Assuming large set of data on both tickets and ticket_updates and a small set of tickets with status 1, I think you can improve Rudi's sub query to below:

mnirwan it wiil improve performace but he need other fields
UserId, AssignedToId, ... DateCreated, Status you code will return tidket id only

Venki,
Since that is a nested expression to just get ticketid and max time it i exactly how it should be, he just did not carry the whole SQL statement from Rudy's example into his post.
Dave