wrong result .or wrong code for what i need :)

[canabatz]

im using paging on this query:

i got this code that returning wrong results:
[CODE=sql](SELECT bid_price, count(*) as n from bidding_details where bid_id=$bid_id GROUP BY bid_price DESC HAVING n = 1 )
UNION ALL
(SELECT bid_price, count(*) as d from bidding_details where bid_id=$bid_id GROUP BY bid_price DESC HAVING d > 1) limit $eu, $limit "; [/CODE]

the limit is working only on the first query ,the second query is running after the first limit ,so if i got 30 results from the first query and 30 results from the second query ,all will be displayed on the first page

if the first query got 50 results the next query will be displayed in the next page!

what im doing wrong?

thanx in davanced!

[Atli]

canabatz.

Please use [code] tags when posting your code examples. (See How to ask a question)

[code=sq l] ...SQL query goes here... [/code]

Thank you.
Moderator

[canabatz]

Pleeeeeeeeeeeas e , some one!!

[Atli]

I'm not sure what you expect to happen.

What should happen is...

Lets say we are working with this data:

mysql> (SELECT MemberID FROM Member WHERE MemberID <= 4)
    -> UNION
    -> (SELECT MemberID FROM Member WHERE MemberID > 4);
+----------+
| MemberID |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
+----------+
8 rows in set (0.00 sec)

If you put a limit at the end there, it will be applied to the entire result set. Like so:

mysql> (SELECT MemberID FROM Member WHERE MemberID <= 4)
    -> UNION
    -> (SELECT MemberID FROM Member WHERE MemberID > 4)
    -> LIMIT 2;
+----------+
| MemberID |
+----------+
|        1 |
|        2 |
+----------+
2 rows in set (0.00 sec)

If you want a specific amount of results from each query, you would have to specify a LIMIT for each query:

mysql> (SELECT MemberID FROM Member WHERE MemberID <= 4 LIMIT 2)
    -> UNION
    -> (SELECT MemberID FROM Member WHERE MemberID > 4 LIMIT 2);
+----------+
| MemberID |
+----------+
|        1 |
|        2 |
|        5 |
|        6 |
+----------+
4 rows in set (0.00 sec)

Is this what you are trying to do?

If not, please try to explain a bit better. Perhaps show what results your are expecting and what you are actually getting.