The line SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 2,1 return ImgId 4
And the line SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT 3,1 returns ImgId 4 to. ImgId 1 is never returned.
Bill Easton wrote:
This is not an error. The results returned by MySQL are correct.
Since each value of CreatedDate in your example occurs twice, "ORDER BY CreatedDate DESC" only says that the ImgId's 2 and 3 should precede ImgId's 1 and 4. The database is free to return ImgId's 2 and 3 in either order and to return 1 and 4 in either order. There's no requirement that it return them in the same order each time, even if you repeat the same query.
If you really want them returned in a consistent order, you have to sort on a key, for example "ORDER BY CreatedDate DESC, ImgId".
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: ORDER BY DESC order different when using LIMIT Date: Sun, 16 Nov 2003 19:04:31 +0100
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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
