Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts

    Selecting latest rows on a join

    So if I have 2 tables: A containing a list of items, and B containing a list of flags for items, where A to B has a 1 to many relationship, what is the best way to get the latest flag when selecting all items? The only way I know how is something like:

    SELECT [columns] FROM A LEFT JOIN (SELECT [columns] FROM B ORDER BY flagID DESC GROUP BY itemID) ON [join conditions]

    So creating that temp table to pull out the last items, then join that. Is there a better way? Or is that how I should go?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    So you want a list of all items, each item listed just once and listing just the most recent flag? This should work (untested):

    Code:
    SELECT *
    FROM A
    JOIN B
    ON A.FKEY_ID = B.ID
    WHERE B.ID = (SELECT MAX(ID) FROM B AS B2 WHERE B2.FKEY_ID = A.ID)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,648 Times in 4,610 Posts
    I read that as saying you have these tables:

    Table A
    -- ID primary key
    -- fieldA
    -- fieldB
    -- fieldC

    Table B
    -- flagID primary key
    -- ID foreign key to A table
    -- fieldX
    -- fieldY

    or something along those lines.

    If so, then this query *might* perform better than Fumigator's answer. It would probably be worth your while to try it both ways and see which MySQL does better with.
    Code:
    SELECT A.field1, A.field2, A.field3, B.fieldX, B.fieldY, M.maxflagid
    FROM A, B, (SELECT id, MAX(flagid) AS maxflagid FROM B GROUP BY id ) AS M
    WHERE A.id = M.id
    AND M.id = B.id 
    AND M.maxflagid = B.flagid
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Ah, so either way, Id need to the temp table in the query (is it called something else? when you use parens to create a table in the question?) and then join on that. Wonderful, thanks much!

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I call 'em subqueries but I'm old school.

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Subquery, that makes MUCH more sense then a temporary table (as you can make temporary tables in SQL, right?).

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,648 Times in 4,610 Posts
    You can make temporary tables in *SOME* databases. They are not part of the SQL language standard, per se. They are also created quite differently in different DBs (e.g., MySQL requires explicit use of the word TEMPORARY in the CREATE TABLE statement; SQL Server just uses a special naming convention.)

    I call what Fumigator and I did "SUB-SELECTs" or "SUBQUERIES" interchangeably. Terribly imprecise of me, I know.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    I call them derived tables or maybe it's better described as a correlated sub query?
    Last edited by bazz; 05-25-2011 at 11:55 PM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,648 Times in 4,610 Posts
    Quote Originally Posted by bazz View Post
    I call them derived tables or maybe it's better described as a correlated sub query?
    SQL Server actually *has* something known as "table-valued functions" that are closer in meaning to "derived tables", so yeah, "correlated sub query" is more generic and I'd use that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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