SELECT and LIMIT question

[Jay K]

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

[Siemel Naran]

"Jay K" <ja*************@yahoo.com> wrote in message news:pJmbd.4953

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
Each statement selects 5 rows, so you have 10 rows total.
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.

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

[Jay K]

Siemel Naran wrote:

"Jay K" <ja*************@yahoo.com> wrote in message news:pJmbd.4953

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

Each statement selects 5 rows, so you have 10 rows total.

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.

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

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

[Bill Karwin]

Jay K wrote:

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

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.