Query statements with two max conditions in SQL

The previous period of time to find a job to take part in the written test, the written question has a SQL query statement, the condition is that two fields are the maximum, the first instinct is two fields (for example, age, hight) equals Max (), with a row subquery. Second instinct is not, if there is exactly one age=max (age), Hight=max (hight) data in the table, the row subquery is no problem. But if the Age=max (age) Data hight! =max (hight) and Hight=max (hight) data age=! What about Max (age)? Then the results must be empty.

I want to first make sure that Age=max (age), in the Age=max (age) record to query Hight=max (hight) records, so should be able to meet test instructions it? (later I looked at other places like I was asking for my first instinct 0.0) and then what? The back is complicated, the query statement is a layer of nesting ... In the end it was just a rough draft, but the written time, the interviewer said forget, the pen test is not important ...

Today we see an example of this problem (he did it with a row subquery) and solved the problem.

Mysql> Create TableMaxtest (ageint, hightint); Query OK,0Rows Affected (0.27sec) MySQL> Insert  intoMaxtestValues( -, the); Query OK,1Row affected (0.05sec) MySQL> Insert  intoMaxtestValues( -,173); Query OK,1Row affected (0.06sec) MySQL> Insert  intoMaxtestValues( +,193); Query OK,1Row affected (0.06sec) MySQL> Insert  intoMaxtestValues( at,199); Query OK,1Row affected (0.06sec) MySQL> Select *  frommaxtest;+------+-------+|Age|Hight|+------+-------+|    - |    the ||    - |   173 ||    + |   193 ||    at |   199 |+------+-------+4Rowsinch Set(0.00sec) MySQL> Select *  fromMaxtestwhere(Age,hight)=(Select Max(age),Max(hight) frommaxtest); EmptySet(0.00sec) MySQL> Select *  fromMaxtestwhereHight=(Select Max(hight) from(Select *  fromMaxtestwhereAge=(Select Max(age) frommaxtest)) a);+------+-------+|Age|Hight|+------+-------+|    + |   193 |+------+-------+1Rowinch Set(0.00sec)

For the first time in the blog Park to express my imaginative achievement here on those written interview questions inexpressible feelings

Query statements with two max conditions in SQL