UNION ALL and row count

[nydiann]

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 ?

[guido2004]

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

[nydiann]

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

[nydiann]

Any solution?

[nydiann]

Solved.

[guido2004]

Could you please post the solution here?

[nydiann]

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

[guido2004]

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