MySQL Forums
Forum List  »  New in 4.1: Subqueries

Wrong results when using LIMIT
Posted by: Claudio Bardelle
Date: September 27, 2005 02:50AM

Hello All,

I have the following query which returns ONE row:

SELECT DISTINCT user.Id, UCASE(Surname), Name, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(Dob)),'%Y')+0 AS Age,0 as Expiration
FROM ( SELECT UserId,Kind FROM transaction,transactiontype
WHERE transactiontype.id=transaction.transactiontypeid
AND transactiontype.kind IN (0,2,1)
AND transaction.enddate>'2005-09-27' GROUP BY userid,kind) AS t1, user
WHERE Surname>='A' AND Surname<'['
AND PrivilegeId=1 HAVING COUNT(UserId)>2 ORDER By Surname,Name LIMIT 0,1000

I get the correct result ONLY when the row_count parameter of the LIMIT clause is less that the number of records in the 'user' table, otherwise the query returns the first record in the user table according to the primary key (which is also the first record actually entered into the table, but I'm not sure whether this matters). Omitting the LIMIT clause returns the same incorrect result.
Am I doing something wrong or might it be a bug? I'm using MySQL 4.1.12 (Linux)

Thank you,
Claudio



Edited 2 time(s). Last edit at 09/27/2005 03:22AM by Claudio Bardelle.

Options: ReplyQuote


Subject
Views
Written By
Posted
Wrong results when using LIMIT
4122
September 27, 2005 02:50AM
2568
September 28, 2005 05:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.