SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UNION ALL and row count

    Hello.

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


    e.g.
    Code SQL:
             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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT 
        COUNT(*) AS total
    FROM
      (SELECT * FROM WORLD_1
       UNION ALL
       SELECT * FROM WORLD_2
       UNION ALL
       SELECT * FROM WORLD_3
      ) AS a

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought, rather, about numbered list, like this one above.

    In single query it can be done with this:

    Code SQL:
    SET @id:= 0;
    SELECT @id:=@id+1 AS id
    , name 
    FROM WORLD_1

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any solution?

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Solved.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Could you please post the solution here?

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    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. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Thanks
    Now we have a question and a solution. This way the post might be helpful to others.


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
  •