Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    79

    Unanswered: Return x number of empty rows on purpose

    Hi,

    It is possible to return a calculated number of empty rows that I can UNION join, with the number of empty rows calculated based on the number of rows returned in the other query? I always want to return 20 rows, and if the number of rows from the first query is less than 20 then the rest will be blank.
    I though of doing this with a calculated LIMIT but I get an error.

    SELECT a,b,c
    FROM table1
    WHERE d>10
    UNION
    SELECT '' AS a, '' AS b, '' AS c
    FROM table1
    LIMIT 20 - (SELECT COUNT(*) FROM table1 WHERE d>10)

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    You cannot use expressions in a LIMIT clause.

    Depending on your data, the following trick might work:

    Code:
    (
    SELECT a,b,c
    FROM table1
    WHERE d>10
    ) UNION ALL (
    SELECT '' AS a, '' AS b, '' AS c 
    FROM table1
    )
    ORDER BY a DESC LIMIT 20;
    --
    felix

  3. #3
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Not sure how MySQL optimizes searches, but if the table is large, I'd assume it's probably not a bad idea to add a limit in the second select statement to prevent a full table scan.


    Code:
    (
    SELECT a,b,c
    FROM table1
    WHERE d>10
    ) UNION ALL (
    SELECT '' AS a, '' AS b, '' AS c 
    FROM table1
    LIMIT 20
    )
    ORDER BY a DESC LIMIT 20;

  4. #4
    Join Date
    Jun 2005
    Posts
    79

    Thumbs up

    Works great! Even without the ORDER BY, just LIMIT by itself

    Can't thank you enough.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •