Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    19

    Unanswered: Select/Grouping Question: Select where the recent of a group of rows equals xx

    I have a "revisions" table that keeps track of versions of an item:

    item_id | version_id | approved
    3 | 334 | 0
    3 | 333 | 1
    3 | 332 | 1
    8 | 556 | 1
    8 | 555 | 1
    9 | 654 | 0
    9 | 653 | 1

    I would like to get a list of Items whose most recent version has not been approved (approved == 0). So in the example data above, my result set would be:
    item_id
    3
    9


    Is this possible to do in a single query? This was my first guess:
    SELECT MAX(version_id), item_id, approved FROM revisions GROUP BY item_id HAVING approved = 0;

    However, I'm 99% positive this is not correct.

    Any thoughts?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by forgueam
    However, I'm 99% positive this is not correct.
    i'm 100% positive

    this is a classic example of "rows holding the groupwise maximum" (if you google this, you will see some interesting results)
    Code:
    SELECT revisions.item_id
         , revisions.version_id
      FROM ( SELECT item_id
                  , MAX(version_id) AS latest_version
               FROM revisions
             GROUP
                 BY item_id ) AS m
    INNER
      JOIN revisions
        ON revisions.item_id    = m.item_id
       AND revisions.version_id = m.latest_version
     WHERE revisions.approved = 0
    by the way, it's nice to see some more iNet people on their own forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    19
    Quote Originally Posted by r937
    this is a classic example of "rows holding the groupwise maximum"
    Thanks Rudy, I was hoping there would be a way to avoid sub-queries, but I guess there probably is not, and a derived table is something I hadn't considered. I'm picking through those search results now.

    Quote Originally Posted by r937
    by the way, it's nice to see some more iNet people on their own forum
    I was a member here well before I started at iNET (that was part of the appeal of the job!). When I've reached the edge of my abilities (which I do abnormally often it seems), it is a great feeling to be able to tap into one of our own communities for expert advice...

    It may be time to convince iNET to buy me a copy of your Simply SQL book

    Thanks again!

  4. #4
    Join Date
    Sep 2004
    Posts
    19
    Here is the query I ended up using (slight modification of Rudy's suggestion)

    Code:
    SELECT item_id
    FROM (
        SELECT MAX(version_id) AS 'version_id' FROM revisions GROUP BY item_id
    ) AS most_recent
    INNER JOIN revisions ON most_recent.version_id = revisions.version_id
    WHERE revisions.approved = 0

Posting Permissions

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