CODEWITHSUNDEEP
mysql
AllisonC
AllisonC

Reputation: 3099

How can I get the max value within a max value in mysql?

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

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)

Thanks

Upvotes: 1

Views: 95

Answers (2)

Mchl
Mchl

Reputation: 62387

This SQL query should provide the max value you want.

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

Upvotes: 0

vaugham
vaugham

Reputation: 1851

this one is compatible MySql 5.5 with no joint tables

SELECT id
FROM cycles
ORDER BY Cycle_Number DESC , Cycle_Day DESC
LIMIT 0 , 1

Regards

Upvotes: 3

Related Questions