I have a schema like the following
我有一个如下的架构
id (INT)
Cycle_Number (INT)
Cycle_Day (INT)
Date (date)
...other columns irrelevant to the question...
How can I get the row that has the max Cycle_Day within the max Cycle_Number
如何在最大Cycle_Number内获得具有最大Cycle_Day的行
For example, say I have the following data
例如,假设我有以下数据
ID Cycle_Number Cycle_Day Date
1 1 1 2011-12-01
2 1 2 2011-12-02
3 2 1 2011-12-03
4 2 2 2011-12-04
5 2 3 2011-12-05
6 2 4 2011-12-06
7 3 1 2011-12-07
8 3 2 2011-12-08
9 3 3 2011-12-09
The query would return row 9. (It has the highest Cycle_Day within the highest Cycle_Number)
查询将返回第9行。(它具有最高Cycle_Number内的最高Cycle_Day)
Thanks
谢谢
2 个解决方案
#1
3
this one is compatible MySql 5.5 with no joint tables
这个兼容MySql 5.5没有联合表
SELECT id
FROM cycles
ORDER BY Cycle_Number DESC , Cycle_Day DESC
LIMIT 0 , 1
Regards
问候
#2
0
This SQL query should provide the max value you want.
此SQL查询应提供所需的最大值。
SELECT ID, Cycle_Number, Cycle_Day, Date
FROM yourTable AS t
CROSS JOIN (
SELECT MAX(Cycle_Number) AS Cycle_Number FROM yourTable
) AS sq USING (Cycle_Number)
ORDER BY Cycle_Day DESC LIMIT 1
#1
3
this one is compatible MySql 5.5 with no joint tables
这个兼容MySql 5.5没有联合表
SELECT id
FROM cycles
ORDER BY Cycle_Number DESC , Cycle_Day DESC
LIMIT 0 , 1
Regards
问候
#2
0
This SQL query should provide the max value you want.
此SQL查询应提供所需的最大值。
SELECT ID, Cycle_Number, Cycle_Day, Date
FROM yourTable AS t
CROSS JOIN (
SELECT MAX(Cycle_Number) AS Cycle_Number FROM yourTable
) AS sq USING (Cycle_Number)
ORDER BY Cycle_Day DESC LIMIT 1