By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,918 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,918 IT Pros & Developers. It's quick & easy.

SELECT and LIMIT question

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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
Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.