I have come across a problem when trying to order certain results by their timestamp value.
尝试按时间戳值排序某些结果时遇到问题。
I would like these results displayed from the newest, to the oldest based on the timestamp values.
我希望这些结果根据时间戳值显示从最新到最旧。
So to explain this, imagine that there were 3 results:
所以要解释一下,想象一下有3个结果:
2012-07-11 17:34:57
2012-07-11 17:33:28
2012-07-11 17:33:07
This result set would be what I would require, but given the following query
这个结果集将是我需要的,但给出以下查询
SELECT timestamp
FROM randomTable
ORDER BY timestamp ASC
I get:
2012-07-11 17:34:57
2012-07-11 17:33:07
2012-07-11 17:33:28
This is as it is sorted by numerical value and 07 comes before 28.
这是按数值排序,07在28之前。
If i sort in descending order I get
如果我按降序排序,我得到
2012-07-11 17:33:07
2012-07-11 17:33:28
2012-07-11 17:34:57
Which is what I am looking for... But it is in reverse.
我正在寻找的是......但它正好相反。
So my question is fairly simple, how could I sort these values in ascending order as I have described?
所以我的问题很简单,我怎么能按照我的描述按升序对这些值进行排序?
EDIT:
EDIT2:
CREATE TABLE `user_quotations` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`quoteNumber` int(100) NOT NULL,
`lastModified` datetime NOT NULL,
`userId` int(100) NOT NULL,
`manufacturer` varchar(250) COLLATE latin1_general_ci NOT NULL,
`modelNumber` varchar(250) COLLATE latin1_general_ci NOT NULL,
`productDesc` varchar(1000) COLLATE latin1_general_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `quoteNumber` (`quoteNumber`,`lastModified`,`userId`,`manufacturer`,`modelNumber`,`timestamp`),
KEY `productDesc` (`productDesc`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
4 个解决方案
#1
12
Your query :
您的查询 :
SELECT timestamp
FROM randomTable
ORDER BY timestamp ASC;
is perfect. But I doubt about the results you have presented in your posting. You posted :
是完美的。但我怀疑你在帖子中提出的结果。你发布了:
2012-07-11 17:34:57
2012-07-11 17:33:07
2012-07-11 17:33:28
But results in your sqlbox shows :
但是你的sqlbox中的结果显示:
2012-07-11 17:34:57
2012-07-15 17:33:07
2012-07-15 17:33:28
Which are perfectly right.
哪个是完全正确的。
Is that a typo error in your posting?
If no, then try the following :
这是你的帖子中的拼写错误吗?如果不是,请尝试以下方法:
SELECT timestamp( `timestamp` ) as 'timestamp'
FROM randomTable
ORDER BY 1 ASC;
#2
4
Check your create statement for the table. I expect your timestamp column is really a string.
检查表的create语句。我希望你的timestamp列真的是一个字符串。
Show create table tablename;
#3
1
if you write the query as:
如果您将查询编写为:
select q.`timestamp`
from user_quotations as q
order by q.`timestamp`
limit 30
you should have them ordered properly.
你应该正确地订购它们。
If not, there is a problem with the timestamp data. Look for leading/ trailing spaces, odd characters, etc.
如果不是,则时间戳数据存在问题。查找前导/尾随空格,奇数字符等。
#4
1
screen shot shows two of the results from 15th and one from 11th. Probably affects the order a wee bit.
屏幕截图显示了第15个和第11个结果中的两个结果。可能会影响顺序。
#1
12
Your query :
您的查询 :
SELECT timestamp
FROM randomTable
ORDER BY timestamp ASC;
is perfect. But I doubt about the results you have presented in your posting. You posted :
是完美的。但我怀疑你在帖子中提出的结果。你发布了:
2012-07-11 17:34:57
2012-07-11 17:33:07
2012-07-11 17:33:28
But results in your sqlbox shows :
但是你的sqlbox中的结果显示:
2012-07-11 17:34:57
2012-07-15 17:33:07
2012-07-15 17:33:28
Which are perfectly right.
哪个是完全正确的。
Is that a typo error in your posting?
If no, then try the following :
这是你的帖子中的拼写错误吗?如果不是,请尝试以下方法:
SELECT timestamp( `timestamp` ) as 'timestamp'
FROM randomTable
ORDER BY 1 ASC;
#2
4
Check your create statement for the table. I expect your timestamp column is really a string.
检查表的create语句。我希望你的timestamp列真的是一个字符串。
Show create table tablename;
#3
1
if you write the query as:
如果您将查询编写为:
select q.`timestamp`
from user_quotations as q
order by q.`timestamp`
limit 30
you should have them ordered properly.
你应该正确地订购它们。
If not, there is a problem with the timestamp data. Look for leading/ trailing spaces, odd characters, etc.
如果不是,则时间戳数据存在问题。查找前导/尾随空格,奇数字符等。
#4
1
screen shot shows two of the results from 15th and one from 11th. Probably affects the order a wee bit.
屏幕截图显示了第15个和第11个结果中的两个结果。可能会影响顺序。