SELECT and LIMIT question

Hi,

I have multiple queries like this:

SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 = 1
ORDER BY col3 desc LIMIT 5

and

SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 = 2
ORDER BY col3 desc LIMIT 5

The only difference is in the WHERE clause table1.col2
= x.

I would like to have one statement instead of multiple
statements and though about using IN clause like:

SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 IN
(1,2,3) ORDER BY col3 desc LIMIT 5

It works without LIMIT, but I need to use the LIMIT.

Any help is appreciated.

Thanks in advance,
Jay

re: SELECT and LIMIT question

"Jay K" <jay_k_architect@yahoo.com> wrote in message news:pJmbd.4953
[color=blue]
> SELECT col1, col2, col3, col4 FROM table1, table2
> where table1.col1 = table2.col1 and table1.col2 = 1
> ORDER BY col3 desc LIMIT 5
>
> and
>
> SELECT col1, col2, col3, col4 FROM table1, table2
> where table1.col1 = table2.col1 and table1.col2 = 2
> ORDER BY col3 desc LIMIT 5[/color]

Each statement selects 5 rows, so you have 10 rows total.
[color=blue]
> The only difference is in the WHERE clause table1.col2
> = x.
>
> I would like to have one statement instead of multiple
> statements and though about using IN clause like:
>
> SELECT col1, col2, col3, col4 FROM table1, table2
> where table1.col1 = table2.col1 and table1.col2 IN
> (1,2,3) ORDER BY col3 desc LIMIT 5
>
> It works without LIMIT, but I need to use the LIMIT.[/color]

This one selects has 5 rows in its result set.


You can try union.

SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 = 1
ORDER BY col3 desc LIMIT 5

union

SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 = 2
ORDER BY col3 desc LIMIT 5

re: SELECT and LIMIT question

Siemel Naran wrote:[color=blue]
> "Jay K" <jay_k_architect@yahoo.com> wrote in message news:pJmbd.4953
>
>[color=green]
>>SELECT col1, col2, col3, col4 FROM table1, table2
>>where table1.col1 = table2.col1 and table1.col2 = 1
>>ORDER BY col3 desc LIMIT 5
>>
>>and
>>
>>SELECT col1, col2, col3, col4 FROM table1, table2
>>where table1.col1 = table2.col1 and table1.col2 = 2
>>ORDER BY col3 desc LIMIT 5[/color]
>
>
> Each statement selects 5 rows, so you have 10 rows total.
>
>[color=green]
>>The only difference is in the WHERE clause table1.col2
>>= x.
>>
>>I would like to have one statement instead of multiple
>>statements and though about using IN clause like:
>>
>>SELECT col1, col2, col3, col4 FROM table1, table2
>>where table1.col1 = table2.col1 and table1.col2 IN
>>(1,2,3) ORDER BY col3 desc LIMIT 5
>>
>>It works without LIMIT, but I need to use the LIMIT.[/color]
>
>
> This one selects has 5 rows in its result set.
>
>
> You can try union.
>
> SELECT col1, col2, col3, col4 FROM table1, table2
> where table1.col1 = table2.col1 and table1.col2 = 1
> ORDER BY col3 desc LIMIT 5
>
> union
>
> SELECT col1, col2, col3, col4 FROM table1, table2
> where table1.col1 = table2.col1 and table1.col2 = 2
> ORDER BY col3 desc LIMIT 5
>
>[/color]

I tried UNION and I am getting this error message:
#1221 - Wrong usage of UNION and ORDER BY

Also, the original query has more than 15 columns to retrieve and 50
queries that need to be joined. The query will be 12k (250bytes x 50).

~Jay

re: SELECT and LIMIT question

Jay K wrote:[color=blue]
> I tried UNION and I am getting this error message:
> #1221 - Wrong usage of UNION and ORDER BY[/color]

Perhaps it's ambiguous whether the last ORDER BY and LIMIT apply to the
last query, or the entire UNION result.

Try using parentheses:

(SELECT ... ORDER BY col3 LIMIT 5)
UNION ALL
(SELECT ... ORDER BY col3 LIMIT 5)

See example and explanation on this page:
http://dev.mysql.com/doc/mysql/en/UNION.html

Regards,
Bill K.