Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: MAX() in WHERE clause

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ticket_updates.*
      FROM tickets
    INNER
      JOIN ( SELECT TicketId
                  , MAX(DateUpdated) AS LastUpdated 
               FROM ticket_updates 
             GROUP 
                 BY TicketId ) AS m
        ON m.TicketId = tickets.TicketId
    INNER
      JOIN ticket_updates
        ON ticket_updates.TicketId = tickets.TicketId 
       AND ticket_updates.DateUpdated = m.LastUpdated
     WHERE tickets.status = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Thanks r937. That worked like a charm!

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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:
    Code:
    SELECT ticket_updates.*
      FROM tickets tck
             ,ticket_updates tck_upd
     WHERE tickets.status = 1
        AND tck_upd.TicketId = tck.TicketId 
        AND tck_upd.DateUpdated = (SELECT MAX(DateUpdated) 
                                                   FROM ticket_updates TCK_UPD1
                                                WHERE TCK_UPD1.TICKETID = TCK.TICKETID)
    Dave

  5. #5
    Join Date
    Sep 2009
    Posts
    64
    dav1mo,

    Your query would mean that the sub query for this section

    Code:
      AND tck_upd.DateUpdated = (SELECT MAX(DateUpdated) 
                                                   FROM ticket_updates TCK_UPD1
                                                WHERE TCK_UPD1.TICKETID = TCK.TICKETID)
    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.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  8. #8
    Join Date
    Sep 2009
    Posts
    64
    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:

    Code:
    SELECT
    	tu.TicketId,
    	MAX(tu.DateUpdated) AS LastUpdated 
    FROM
    	ticket_updates tu
    	JOIN tickets t ON t.TicketId = tu.TicketId
    		AND t.status = 1
    GROUP BY
    	tu.TicketId

  9. #9
    Join Date
    Nov 2009
    Posts
    2
    mnirwan it wiil improve performace but he need other fields
    UserId, AssignedToId, ... DateCreated, Status you code will return tidket id only

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •