Hi,
The version of MySQL is (this could be it, I'll try upgrading this now):-
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.
Christian Sage wrote:
>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
>
--
"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
