Hi,
> Order By clause without Limit returns:-
> A
> B
> C
> C
> C
> C
> C
> C
> M
> N
> T
> W
>
> Order By clause with Limit returns:-
>
> C
> C
> C
> C
> C
> C
> M
> N
> T
> W
>
> Is there any way to fix this, so that the results with the limit comes
> out starting with the A and then moves on to the next pages?
Hadn't thought about this before, but what it means (I guess) is that LIMIT
works on the selection, not on the presentation.
What I'm trying to say is that when you consider the sequence in which a
SELECT statement is carried out by the database, an ORDER BY is the second
but last thing that's done (the last being a further restriction of the
result set through a HAVING clause, if present). At the time of the ordering
you already have a result set for the query, and the database is now only
working on the way this result set is presented to the user.
I presume that LIMIT applies to the gathering of the result set, i.e. you
get the required number of rows from the query up to and including the WHERE
clause. Only then it gets ordered, but if your result set does not contain
the records with an 'A' they can't get ordered either. Don't know whether
I'm explaining this very well, but perhaps you get the idea.
...
Have gone and tested it now (3.23.42 on Win2K). Here is the SQL text and the
results:
---< cut
DROP DATABASE IF EXISTS ordertest;
CREATE DATABASE ordertest;
USE ordertest;
CREATE TABLE letters (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
letter CHAR NOT NULL
);
INSERT INTO letters
(letter)
VALUES
('C'),
('C'),
('C'),
('C'),
('C'),
('C'),
('M'),
('N'),
('T'),
('W'),
('A'),
('B');
SELECT letter
FROM letters
LIMIT 10;
+--------+
| letter |
+--------+
| C |
| C |
| C |
| C |
| C |
| C |
| M |
| N |
| T |
| W |
+--------+
10 rows in set (0.00 sec)
SELECT letter
FROM letters
ORDER BY letter
LIMIT 10;
+--------+
| letter |
+--------+
| A |
| B |
| C |
| C |
| C |
| C |
| C |
| C |
| M |
| N |
+--------+
10 rows in set (0.37 sec)
--< cut
Surprise, surprise, turns out MySQL behaves not the way I thought it would,
but rather the way you thought it should. Are you perhaps running a
different version? And could you perhaps show us your table structure(s) and
query so that we can find out what's really happening there?
Cheers,
Christian Sage
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
