469,568 Members | 1,361 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

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
Jul 20 '05 #1
3 2391
"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
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
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.

Similar topics

3 posts views Thread by Bob Bedford | last post: by
10 posts views Thread by Randell D. | last post: by
3 posts views Thread by Wim Roffil | last post: by
12 posts views Thread by Alex | last post: by
3 posts views Thread by Tcs | last post: by
5 posts views Thread by Nick Weisser | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.