SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Query to retrieve latest row from 8 tables at once?

    Hi,

    I have 8 tables. I would like to retrieve the latest (newest) row from each table. I'd like to achieve this in a single, efficient query considering that each of the 8 tables will run into the millions of records each. In pseudo-code:

    Code:
    (SELECT * WHERE table = lastRowCreated)*8tables
    And the returned value would equate to:
    Code:
    $latest = array(
                    'record1' => array($latestRecordTable1), 
                    'record2' => array($latestRecordTable2), 
                    'record3' => array($latestRecordTable3), 
                    'record4' => array($latestRecordTable4), 
                    'record5' => array($latestRecordTable5), 
                    'record6' => array($latestRecordTable6), 
                    'record7' => array($latestRecordTable7), 
                    'record8' => array($latestRecordTable8)
                );
    Thanks a lot for any assistance!

    Cranjed

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)

  3. #3
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. This was the solution:

    Code:
        $sql = "(SELECT * FROM level1)
                UNION
                (SELECT * FROM level2)
                UNION
                (SELECT * FROM level3)
                UNION
                (SELECT * FROM level4)
                UNION
                (SELECT * FROM level5)
                UNION
                (SELECT * FROM level6)
                UNION
                (SELECT * FROM level7)
                UNION
                (SELECT * FROM level8)
                ORDER BY id DESC LIMIT 8;";

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cranjled View Post
    I'd like to achieve this in a single, efficient query
    each SELECT in the UNION will return all (millions of) rows in that table

    then all rows from all tables must be sorted together (requiring massive amounts of temporary workspace)

    only then the top 8 can be retrieved

    this is efficient ???????????????

    and anyhow, what if one of those tables has a lot of recent rows while the others don't -- the latest 8 rows will all come from that table

    thus the UNION doesn't even provide the right answer !!!


    i'll make a small bet that 8 separate queries, assuming each table has an index on the date column, is what you really want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    How about this?
    Code MySQL:
    (SELECT 
         1 AS sort_col
       , t1.* 
     FROM t1
     ORDER BY datecolumn DESC
     LIMIT 1)
    UNION
    (SELECT 
         2 AS sort_col
       , t2.* 
     FROM t2
     ORDER BY datecolumn DESC
     LIMIT 1)
    UNION
    ...
    UNION
    (SELECT 
         8 AS sort_col
       , t8.* 
     FROM t8
     ORDER BY datecolumn DESC
     LIMIT 1)
    ORDER BY sort_col

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's excellent, and will certainly outperform 8 separate queries

    except of course if the subquery isn't allowed to use LIMIT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    that's excellent, and will certainly outperform 8 separate queries

    except of course if the subquery isn't allowed to use LIMIT
    Well, in that case they'll just have to upgrade to a decently recent version of MySQL

  8. #8
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the responses! Just wanted to add that I'd figured out how to further tweak the query. I'll have to look at that nice example above closely!! Anyway, here's the tweak, which does this: pulls a random record from each of the 8 tables (and seems to) ensure a single record is pulled from each. What say you guys about this one?

    Code:
    (SELECT * FROM level1 LIMIT 1) UNION (SELECT * FROM level2 LIMIT 1) UNION (SELECT * FROM level3 LIMIT 1) UNION (SELECT * FROM level4 LIMIT 1) UNION (SELECT * FROM level5 LIMIT 1) UNION (SELECT * FROM level6 LIMIT 1) UNION (SELECT * FROM level7 LIMIT 1) UNION (SELECT * FROM level8 LIMIT 1) ORDER BY RAND() LIMIT 8;
    Appreciate your feedback(s)...thanks!!

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cranjled View Post
    pulls a random record from each of the 8 tables
    No it doesn't. It pulls the first record from each table.
    If you want a random record from each table, put the ORDER BY RAND in each single SELECT.
    (and seems to) ensure a single record is pulled from each.
    Yes it does.
    What say you guys about this one?

    Code:
    (SELECT * FROM level1 LIMIT 1) UNION (SELECT * FROM level2 LIMIT 1) UNION (SELECT * FROM level3 LIMIT 1) UNION (SELECT * FROM level4 LIMIT 1) UNION (SELECT * FROM level5 LIMIT 1) UNION (SELECT * FROM level6 LIMIT 1) UNION (SELECT * FROM level7 LIMIT 1) UNION (SELECT * FROM level8 LIMIT 1) ORDER BY RAND() LIMIT 8;
    You can get rid of that LIMIT 8, because the result of all the unions is always 8 lines at the most.

    This isn't at all what you were asking for in your OP though

  10. #10
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    guido, thanks. You misunderstand my last posts. In fact, your example query has led to a visually noticeable performance increase in retrieving the needed records -- THANK YOU!

    Yes, your first query does pull the latest record from each table; I agree. That clarified, I was referring to the next query I posted on this thread (the post at 22:53pm, which was a variation on your original query).

    You're also right in that this was not the original question... but as it was something very similar that I ALSO needed to achieve, I thought I'd post the other query as well, so that both queries could be shown -- or shot down -- here. It's always good to find relatively like things together.

    Anyway, here's the final 2 queries that I'm using, and if you can see a better way still, I'm happy to hear your advice! Did my best to format the queries, sorry they're not perfect.

    Code:
    // Get latest record from each of X tables in a single query.
    (SELECT 
        1 AS difficulty, 
        level1.* 
     FROM level1 
     ORDER BY id DESC 
     LIMIT 1) 
    UNION 
    (SELECT 
        2 AS difficulty, 
        level2.* 
     FROM level2 
     ORDER BY id DESC 
     LIMIT 1) 
    UNION 
    ...etc...
    (SELECT 
        8 AS difficulty, 
        level8.* 
     FROM level8 
     ORDER BY id DESC 
     LIMIT 1)
    ORDER BY difficulty;
    Code:
    // Get a single random record from each of X tables in a single query.
    (SELECT 
        1 AS sort_col, 
        t1.* 
     FROM t1 
     ORDER BY RAND() 
     LIMIT 1) 
    UNION 
    (SELECT 
        2 AS sort_col, 
        t2.* 
     FROM t2 
     ORDER BY RAND() 
     LIMIT 1) 
    UNION 
    ...etc...
    (SELECT 
        8 AS sort_col, 
        t8.* 
     FROM t8 
     ORDER BY RAND() 
     LIMIT 1)  
    ORDER BY sort_col;

  11. #11
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, I detect a bit of sarcasm in your post... usually, that's not all that nice, but I know you mean well. Just wanted to say thanks for jumping in and giving me a laugh with the "this is efficient ???????????????" comment... I appreciate the great deal of help you've given me here, thank you.


Bookmarks

Posting Permissions

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