Get the highest views from all tables

topdown.me · 11-08-2012, 08:16 PM

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?

Old Pedant · 11-08-2012, 08:46 PM

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.

Old Pedant · 11-08-2012, 08:47 PM

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.

topdown.me · 11-09-2012, 12:48 PM

Quote:

Originally Posted by Old Pedant

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?

Old Pedant · 11-09-2012, 08:52 PM

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