www.webdeveloper.com
Results 1 to 2 of 2

Thread: nested query; query within a query

  1. #1
    Join Date
    Apr 2011
    Posts
    15

    nested query; query within a query

    Data


    id case_id date_notated
    1 25 06/01/2010
    2 25 06/21/2011
    3 25 09/03/2012
    4 25 05/01/2013
    5 26 05/02/2013
    6 26 05/04/2013
    7 26 05/05/2013
    8 26 05/07/2013


    Desired Result

    id case_id last_post
    4 25 05/01/2013
    8 26 05/07/2013

    how do i write a query to get the result?

    thanks!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,176
    If I'm guessing correctly as to what the criteria are:
    Code:
    SELECT id, case_id, last_post
    FROM table_name AS t1
    WHERE last_post = (
      SELECT MAX(last_post)
      FROM table_name t2
      WHERE t1.case_id = t2.case_id
    )
    ORDER BY case_id -- assuming you want this ordering, otherwise change this
    This assumes that last_post is some sort of DATE, DATETIME, or TIMESTAMP column.

    You'll want to make sure you have indexes on the case_id and last_post columns for best performance.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles