Return x number of empty rows on purpose

PolarBear2k · #1 (**permalink**) 10-21-05, 14:04

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)

felixg · #2 (**permalink**) 10-21-05, 14:57

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

jfulton · #3 (**permalink**) 10-21-05, 15:19

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;

PolarBear2k · #4 (**permalink**) 10-21-05, 15:45

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

Can't thank you enough.

Internet Services

Web Development

Digital Marketing

Consumer Tech