List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 9 2003 6:36pm
Subject:Re: alias a select query
View as plain text  
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.

Thread
alias a select queryHu Qinan9 Mar
  • Re: alias a select queryPaul DuBois9 Mar