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 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Get the highest views from all tables

    Hello
    I have 2 tables and both of them has "views" column.
    I want to use PHP and run a query that will show the highest views content from this two tables, mixed.

    How I can do such a query?
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Code:
    SELECT MAX(U.views) FROM (
        SELECT views FROM table1
        UNION 
        SELECT views FROM table2
    ) AS U
    You *might* get better performance from:
    Code:
    SELECT MAX(U.maxview) FROM (
        SELECT MAX(views) AS maxview FROM table1
        UNION 
        SELECT MAX(views) AS maxview FROM table2
    ) AS U
    You can try it both ways.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    And it has nothing to do with PHP, per se. The query will do what you want by itself and could be used standalone, with PHP, with ASP, with JSP, etc., etc.
    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
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Code:
    SELECT MAX(U.views) FROM (
        SELECT views FROM table1
        UNION 
        SELECT views FROM table2
    ) AS U
    You *might* get better performance from:
    Code:
    SELECT MAX(U.maxview) FROM (
        SELECT MAX(views) AS maxview FROM table1
        UNION 
        SELECT MAX(views) AS maxview FROM table2
    ) AS U
    You can try it both ways.
    It will show only one reslt, doesnt it?
    And if I want to show the the top 3?

    edit:
    I found out how to do it and it works,
    PHP Code:
    $getcontent "SELECT .. FROM
    ( SELECT .. FROM moviesdb
    UNION ALL
    SELECT  ... FROM gamesdb ORDER BY views DESC LIMIT 3) views"

    but I got anothing problem.
    I need to check from what databse I got the result, and I have no idea how to do it and where to place the "AS" or these things.
    help?
    Last edited by topdown.me; 11-09-2012 at 01:10 PM.
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Code:
    SELECT U.views, U.fromTable
    FROM ( 
        SELECT views, 'table1' AS fromTable FROM table1 
        UNION 
        SELECT views, 'table2' FROM table2 
    ) AS U
    ORDER BY views DESC
    LIMIT 3
    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
    •