UNION ALL and row count

Databases
1

Hello.

I am wondering whether is possible to get a row count with SELECT Statement including UNION ALL.

e.g.

 
         SELECT * FROM WORLD_1 
UNION ALL
SELECT * FROM WORLD_2
UNION ALL
SELECT * FROM WORLD_3

And get a row count for a full resultset.

1. Richie WORLD_1
2. Caroline WORLD_3
3. Stephen WORLD_2
4. Stuard WORLD_1
5. Etheyn WORLD_2

In simple query , it can be done by one variable setting.
But here , perhaps could it be done with SQL_CALC_FOUND_ROWS ?

2 

SELECT
    COUNT(*) AS total
FROM
  (SELECT * FROM WORLD_1
   UNION ALL
   SELECT * FROM WORLD_2
   UNION ALL
   SELECT * FROM WORLD_3
  ) AS a
3

I thought, rather, about numbered list, like this one above.

In single query it can be done with this:


SET @id:= 0;
SELECT @id:=@id+1 as id
, name
FROM WORLD_1
4

Any solution?

5

Solved.

6

Could you please post the solution here?

7 
SET @rank:= 0;
SELECT @rank:=@rank+1 as rowid
, name
, lastname
, address
, date
from (
( SELECT * FROM WORLD_1 )
UNION all
( SELECT * FROM WORLD_2 )
UNION all
( SELECT * FROM WORLD_3 )
     ) t1
ORDER
   BY date
DESC LIMIT 1000
8

Thanks :slight_smile:
Now we have a question and a solution. This way the post might be helpful to others.