>Description:
I have a table full of pages for a church website I am developing. Each has
some data (text) and a title, page id, updated (date) and created(date). I
want to select a list of pages with their id and title and updated date
according to when they were created (by created). When I select the id,
title, and updated date and sort by created, it displays the order wrong.
When I also tell it to select created, then it does it correctly. A
transcript illustrating is shown below.
# here is the table scheme. Nothing extraordinary.
mysql> describe pages;
+------------------+------------------+------+-----+------------+-----------
-----+
| Field | Type | Null | Key | Default | Extra
|
+------------------+------------------+------+-----+------------+-----------
-----+
| pID | int(10) unsigned | | PRI | NULL |
auto_increment |
| title | varchar(255) | | | |
|
| category | int(10) unsigned | | | 0 |
|
| updated | date | | | 0000-00-00 |
|
| created | date | | | 0000-00-00 |
|
| body | mediumtext | | | |
|
| isCategory | enum('y','n') | | | n |
|
| openToDiscussion | enum('y','n') | | | n |
|
| isArchived | enum('y','n') | | | n |
|
| isPlainText | enum('y','n') | | | n |
|
+------------------+------------------+------+-----+------------+-----------
-----+
10 rows in set (0.00 sec)
# here is a dump of the data for the table, excluding the actual text since
that would take up too much space
mysql> select pID, title, category, updated, created, isCategory,
openToDiscussion, isArchived, isPlainText from pages;
+-----+-----------------------------------------------------------------+---
-------+------------+------------+------------+------------------+----------
--+-------------+
| pID | title |
category | updated | created | isCategory | openToDiscussion |
isArchived | isPlainText |
+-----+-----------------------------------------------------------------+---
-------+------------+------------+------------+------------------+----------
--+-------------+
| 13 | Passion Week - 4/10/2001 |
41 | 2001-10-22 | 2001-04-10 | n | n | n |
y |
| 27 | Website Information |
44 | 2001-10-15 | 2001-10-14 | n | n | n |
n |
| 12 | Divine sovereignty and human responsibility - 7/21/2001 |
41 | 2001-10-21 | 2001-07-21 | n | n | n |
n |
| 8 | __siteheader__ |
2 | 2001-10-15 | 2001-09-03 | n | n | n | n
|
| 10 | Welcome to NWBC |
1 | 2001-10-21 | 2001-09-16 | y | n | n | n
|
| 14 | Friendship with the world - 8/30/01 |
41 | 2001-10-22 | 2001-08-30 | n | n | n |
n |
| 15 | Escaping from Giant Despair - 8/22/01 |
41 | 2001-10-22 | 2001-08-22 | n | n | n |
n |
| 16 | Directions to NWBC |
44 | 2001-09-16 | 2001-09-16 | n | n | n |
n |
| 17 | Doctrine |
45 | 2001-10-09 | 2001-09-16 | y | n | n |
n |
| 18 | Information |
44 | 2001-09-16 | 2001-09-16 | y | n | n |
n |
| 19 | Doctrinal Statement |
45 | 2001-10-16 | 2001-09-16 | n | n | n |
n |
| 50 | The Fear of Death 10-17-01 |
41 | 2001-10-17 | 2001-10-17 | n | n | n |
n |
| 20 | Announcements |
43 | 2001-10-16 | 2001-09-16 | y | n | n |
n |
| 21 | A Biblical Response to Disaster |
56 | 2001-10-21 | 2001-09-16 | n | n | n |
n |
| 22 | 2 Sam. 7: Davids Praise to God |
48 | 2001-10-17 | 2001-09-18 | n | n | n |
y |
| 23 | 2 Sam. 8: The Lord is our Helper |
48 | 2001-09-18 | 2001-09-18 | n | n | n |
y |
| 24 | 2 Sam. 9: Unmerited covenant grace |
48 | 2001-10-15 | 2001-09-18 | n | n | n |
n |
| 25 | Christian Sites |
49 | 2001-10-08 | 2001-10-08 | n | n | n |
n |
| 26 | Full-text search |
50 | 2001-10-14 | 2001-10-14 | y | n | n |
n |
| 28 | Sermons |
42 | 2001-10-15 | 2001-10-14 | y | n | n |
n |
| 29 | I Can't But He Can 2/18/2000 |
41 | 2001-10-22 | 2001-02-18 | n | n | n |
y |
| 30 | The Gift of Tongues? |
46 | 2001-10-21 | 2001-10-14 | n | n | n |
n |
| 31 | Whosoever Will |
46 | 2001-10-21 | 2001-10-14 | n | n | n |
n |
| 49 | The Lord is my Shepherd 9-5-01 |
41 | 2001-10-21 | 2001-09-05 | n | n | n |
n |
| 33 | I Can't but He Can 2-18-00 |
41 | 2001-10-22 | 2000-02-18 | n | n | n |
y |
| 34 | Freewill and Common Sense 2-23-00 |
41 | 2001-10-22 | 2000-02-23 | n | n | n |
y |
| 35 | The Deep Joy of Jesus 3-09-00 |
41 | 2001-10-22 | 2000-03-09 | n | n | n |
y |
| 36 | What is an Evangelical? 3-23-00 |
41 | 2001-10-22 | 2000-03-23 | n | n | n |
y |
| 37 | Rejoicing in Justification 4-7-00 |
41 | 2001-10-22 | 2000-04-07 | n | n | n |
y |
| 38 | The Blessedness of Being Children of God 4-14-00 |
41 | 2001-10-22 | 2000-04-14 | n | n | n |
y |
| 39 | Resurrection Sunday: dare I call it Easter 4/23/00 |
41 | 2001-10-22 | 2000-04-23 | n | n | n |
y |
| 40 | Revivals and Prayer 6-1-00 |
41 | 2001-10-22 | 2000-06-01 | n | n | n |
y |
| 41 | Fast and Pray 6-7-00 |
41 | 2001-10-15 | 2001-10-15 | n | n | |
y |
| 42 | Christianity in Crisis by Hank Hannegraf 6-22-00 |
41 | 2001-10-15 | 2001-10-15 | n | n | |
y |
| 43 | Tasting the Lord 7-18-00 |
41 | 2001-10-15 | 2001-10-15 | n | n | |
y |
| 44 | Delighting in the Lord 7-26-00 |
41 | 2001-10-22 | 2000-07-26 | n | n | n |
y |
| 45 | Thirsting for God 8-2-00 |
41 | 2001-10-15 | 2001-10-15 | n | n | |
y |
| 46 | Loving God 8-18-00 |
41 | 2001-10-15 | 2001-10-15 | n | n | |
y |
| 47 | Green Beret Christians 8-24-00 |
41 | 2001-10-22 | 2000-08-24 | n | n | n |
n |
| 48 | Marathon Christianity 9-20-00 |
41 | 2001-10-21 | 2000-09-20 | n | n | n |
n |
| 51 | 2 Sam. 10: Stirring up Strife |
48 | 2001-10-20 | 2001-10-20 | n | n | n |
n |
| 52 | 2 Sam. 11:1-5 Bathsheba |
48 | 2001-10-20 | 2001-10-20 | n | n | n |
n |
| 53 | 2001 Sermons |
53 | 2001-10-20 | 2001-10-20 | y | n | n |
n |
| 54 | II Samuel |
48 | 2001-10-21 | 2001-10-20 | y | n | n |
n |
| 55 | 2 Sam. 11:6-27 Sin breeds more sin |
48 | 2001-10-20 | 2001-10-20 | n | n | n |
n |
| 56 | 2 Sam. 12:1-15 God's faithfulness to restore his sinning child |
48 | 2001-10-20 | 2001-10-20 | n | n | n |
n |
| 57 | 2 Sam. 12:13 Repentance and its Benefits |
48 | 2001-10-20 | 2001-10-20 | n | n | n |
n |
| 58 | 2 Sam. 12:13-31 Enduring God's Discipline |
48 | 2001-10-20 | 2001-10-20 | n | n | n |
n |
+-----+-----------------------------------------------------------------+---
-------+------------+------------+------------+------------------+----------
--+-------------+
48 rows in set (0.00 sec)
# Here is the query I am running on my website. After inspecting the table
# below (how they should be ordered) you can see that the order of these is
incorrect
# (not by created). as for the pID <> '' -- sometimes there will be a number
there
# depending on the page, but not always (as in this case) it should have no
effect
mysql> SELECT pID, title, updated FROM pages WHERE category = '41' AND pID
<> '' AND isCategory = 'n' AND isArchived = 'n' ORDER BY 'created';
+-----+---------------------------------------------------------+-----------
-+
| pID | title | updated
|
+-----+---------------------------------------------------------+-----------
-+
| 13 | Passion Week - 4/10/2001 | 2001-10-22
|
| 12 | Divine sovereignty and human responsibility - 7/21/2001 | 2001-10-21
|
| 14 | Friendship with the world - 8/30/01 | 2001-10-22
|
| 15 | Escaping from Giant Despair - 8/22/01 | 2001-10-22
|
| 50 | The Fear of Death 10-17-01 | 2001-10-17
|
| 29 | I Can't But He Can 2/18/2000 | 2001-10-22
|
| 49 | The Lord is my Shepherd 9-5-01 | 2001-10-21
|
| 33 | I Can't but He Can 2-18-00 | 2001-10-22
|
| 34 | Freewill and Common Sense 2-23-00 | 2001-10-22
|
| 35 | The Deep Joy of Jesus 3-09-00 | 2001-10-22
|
| 36 | What is an Evangelical? 3-23-00 | 2001-10-22
|
| 37 | Rejoicing in Justification 4-7-00 | 2001-10-22
|
| 38 | The Blessedness of Being Children of God 4-14-00 | 2001-10-22
|
| 39 | Resurrection Sunday: dare I call it Easter 4/23/00 | 2001-10-22
|
| 40 | Revivals and Prayer 6-1-00 | 2001-10-22
|
| 44 | Delighting in the Lord 7-26-00 | 2001-10-22
|
| 47 | Green Beret Christians 8-24-00 | 2001-10-22
|
| 48 | Marathon Christianity 9-20-00 | 2001-10-21
|
+-----+---------------------------------------------------------+-----------
-+
18 rows in set (0.01 sec)
# Now, This query is identical to the last, except I went ahead and selected
# the created field along with the others, even though I do not even need
# that field. Now it displays in the correct order (according to created)
mysql> SELECT pID, title, updated, created FROM pages WHERE category = '41'
AND pID <> '' AND isCategory = 'n' AND isArchived = 'n' ORDER BY 'created';
+-----+---------------------------------------------------------+-----------
-+------------+
| pID | title | updated
| created |
+-----+---------------------------------------------------------+-----------
-+------------+
| 33 | I Can't but He Can 2-18-00 | 2001-10-22
| 2000-02-18 |
| 34 | Freewill and Common Sense 2-23-00 | 2001-10-22
| 2000-02-23 |
| 35 | The Deep Joy of Jesus 3-09-00 | 2001-10-22
| 2000-03-09 |
| 36 | What is an Evangelical? 3-23-00 | 2001-10-22
| 2000-03-23 |
| 37 | Rejoicing in Justification 4-7-00 | 2001-10-22
| 2000-04-07 |
| 38 | The Blessedness of Being Children of God 4-14-00 | 2001-10-22
| 2000-04-14 |
| 39 | Resurrection Sunday: dare I call it Easter 4/23/00 | 2001-10-22
| 2000-04-23 |
| 40 | Revivals and Prayer 6-1-00 | 2001-10-22
| 2000-06-01 |
| 44 | Delighting in the Lord 7-26-00 | 2001-10-22
| 2000-07-26 |
| 47 | Green Beret Christians 8-24-00 | 2001-10-22
| 2000-08-24 |
| 48 | Marathon Christianity 9-20-00 | 2001-10-21
| 2000-09-20 |
| 29 | I Can't But He Can 2/18/2000 | 2001-10-22
| 2001-02-18 |
| 13 | Passion Week - 4/10/2001 | 2001-10-22
| 2001-04-10 |
| 12 | Divine sovereignty and human responsibility - 7/21/2001 | 2001-10-21
| 2001-07-21 |
| 15 | Escaping from Giant Despair - 8/22/01 | 2001-10-22
| 2001-08-22 |
| 14 | Friendship with the world - 8/30/01 | 2001-10-22
| 2001-08-30 |
| 49 | The Lord is my Shepherd 9-5-01 | 2001-10-21
| 2001-09-05 |
| 50 | The Fear of Death 10-17-01 | 2001-10-17
| 2001-10-17 |
+-----+---------------------------------------------------------+-----------
-+------------+
18 rows in set (0.00 sec)
So my workaround is to go ahead and select created even though I don't need
that piece of data in my result set. One obviously shouldn't have to select
unneeded data just to get it to sort correctly.
>Submitter-Id: ?
>Originator: Luke Loeffler
>Organization: Deafdog Studio
>MySQL support: none
>Synopsis: ordering incorrect unless order by column also selected
>Severity: critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.36 (Source distribution)
>Environment:
System: RedHat Linux 7.1 www.deafdog.net 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT
2001 i686 unknown
Architecture: i686
---------------------------------------------------------------------
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
