At 23:52 +0800 3/9/03, Hu Qinan wrote:
>Dear all,
>
>A table "book" looks like this:
>
>"page" "content"
>--------------
>1 abc
>1 de
>2 fgh
>3 ijk
>3 lmn
>3 opq
>...
>210 z
>
>I need a query to randomly select 90% of all pages and their
>corresponding contents.
>
>I have written the following querys. But I have problems to combine
>them into a single one. (I use mysql.)
>
>// Randomly select 40 pages of the book.
>Query1: SELECT DISTINCT page FROM tbl ORDER BY RAND() LIMIT 40;
>
>// Count the total pages of the book. But how to use this value in the
>LIMIT clause in the above query to indicate 90% of all pages?
>Query2: SELECT COUNT(DISTINCT page) FROM book;
Using nothing but SQL, you cannot. You could select the value of COUNT()
into a SQL variable, like this:
SELECT @c := COUNT(DISTINCT page) FROM book;
But you cannot use @c in a LIMIT clause, because the argument to LIMIT
must be an integer constant.
If you issue your queries from within a script, you can use the programming
language to get the COUNT() value, and then insert that value into the
next query so that it appears as an integer constant to MySQL.
>
>// How to embed/alias Query1 in Query3?
>Query3: SELECT * FROM book WHERE page IN Q1;
>
>Thanks a lot.
