Hi I am not sure how this "limit" works , but in oracle if you use a rownum<100 (guess it is some of the same),
the sorting are done After the limitation so... if u say rownum<100 , it gives u 100 lines abitrary order and then it sorts that 100 lines HTH Kim G. Pedersen >>Description: > > When using ORDER BY column DESC the lines are shown in one order but when > I use LIMIT 0,1 the second line insted of the first line are shown. > And when I use LIMIT 2,1 the same line as if use LIMIT 3,1 are shown. > The table has 4 rows. >>How-To-Repeat: > > mysql> SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC; > +-------+-------------+ > | ImgId | CreatedDate | > +-------+-------------+ > | 2 | 2002-11-22 | > | 3 | 2002-11-22 | > | 1 | 2002-11-21 | > | 4 | 2002-11-21 | > +-------+-------------+ > 4 rows in set (0.00 sec) > > mysql> SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT > 0,1; > +-------+-------------+ > | ImgId | CreatedDate | > +-------+-------------+ > | 3 | 2002-11-22 | > +-------+-------------+ > 1 row in set (0.00 sec) > > mysql> SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT > 1,1; > +-------+-------------+ > | ImgId | CreatedDate | > +-------+-------------+ > | 2 | 2002-11-22 | > +-------+-------------+ > 1 row in set (0.00 sec) > > mysql> SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT > 2,1; > +-------+-------------+ > | ImgId | CreatedDate | > +-------+-------------+ > | 4 | 2002-11-21 | > +-------+-------------+ > 1 row in set (0.00 sec) > > mysql> SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT > 3,1; > +-------+-------------+ > | ImgId | CreatedDate | > +-------+-------------+ > | 4 | 2002-11-21 | > +-------+-------------+ > 1 row in set (0.00 sec) > > mysql> SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT > 0,4; > +-------+-------------+ > | ImgId | CreatedDate | > +-------+-------------+ > | 2 | 2002-11-22 | > | 3 | 2002-11-22 | > | 1 | 2002-11-21 | > | 4 | 2002-11-21 | > +-------+-------------+ > 4 rows in set (0.00 sec) > > mysql> DESCRIBE dogge; > +-------------+------------------+------+-----+------------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +-------------+------------------+------+-----+------------+----------------+ > | ImgId | int(10) unsigned | | PRI | NULL | > auto_increment | > | CreatedDate | date | | MUL | 0000-00-00 | > | > +-------------+------------------+------+-----+------------+----------------+ > 2 rows in set (0.00 sec) > > mysql> SELECT * FROM dogge\G > *************************** 1. row *************************** > ImgId: 1 > CreatedDate: 2002-11-21 > *************************** 2. row *************************** > ImgId: 4 > CreatedDate: 2002-11-21 > *************************** 3. row *************************** > ImgId: 2 > CreatedDate: 2002-11-22 > *************************** 4. row *************************** > ImgId: 3 > CreatedDate: 2002-11-22 > 4 rows in set (0.00 sec) > >>Fix: > > Don't know >>Submitter-Id: <submitter ID> >>Originator: Martin Gillstrom >>Organization: > >>MySQL support: none >>Synopsis: ORDER BY DESC order different when using LIMIT >>Severity: serious >>Priority: medium >>Category: mysql >>Class: >>Release: mysql-4.0.16-standard (Official MySQL RPM) > >>C compiler: 2.95.3 >>C++ compiler: 2.95.3 >>Environment: > > System: Linux server1.bymartin.com 2.4.20-20.7 #1 Mon Aug 18 14:56:30 EDT > 2003 i686 unknown > Architecture: i686 > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > /usr/bin/cc > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs > gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-113) > Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' > CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce > -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' > ASFLAGS='' > LIBC: > lrwxrwxrwx 1 root root 13 Mar 21 2003 /lib/libc.so.6 -> > libc-2.2.5.so > -rwxr-xr-x 1 root root 1260480 Mar 5 2003 /lib/libc-2.2.5.so > -rw-r--r-- 1 root root 2312442 Mar 5 2003 /usr/lib/libc.a > -rw-r--r-- 1 root root 178 Mar 5 2003 /usr/lib/libc.so > Configure command: ./configure '--disable-shared' > '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' > '--with-server-suffix=-standard' '--without-embedded-server' > '--without-berkeley-db' '--with-innodb' '--without-vio' > '--without-openssl' '--enable-assembler' '--enable-local-infile' > '--with-mysqld-user=mysql' > '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' > '--with-extra-charsets=complex' '--exec-prefix=/usr' > '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' > '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' > '--infodir=/usr/share/info' '--includedir=/usr/include' > '--mandir=/usr/share/man' '--enable-thread-safe-client' > '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 > -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce > -felide-constructors -fno-exceptions -fno-rtti ' 'CXX=' > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
