As much as I would think it would be.. Could you think of any reason why
it wouldnt work then for me with the following:-
The version of MySQL is:-
3.23.41 on Linux kernel version 2.4.7-2 on a Redhat 8 server.
The table (table1) has the following fields:-
author varchar(128)
category varchar(64)
title varchar(128)
location varchar(64)
platform varchar(40)
note varchar(128)
The query I tried is:-
SELECT * from table1 WHERE title LIKE %keyword% ORDER BY title LIMIT 0,10;
Any help would be greatly appreciated.
karel pitra wrote:
>
>it's not true, order by is performed before limit. if it was the other
>way round , limit would be almost useless
>
>
>>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
>>
--
"Wise men talk because they have something to say; fools talk
because they have to say something." - Plato
Ashwin Kutty
Systems Administrator
Dalhousie University Libraries
(902) 494-2694
---------------------------------------------------------------------
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
