How to select a field corresponding to the tuple in which max(anotehrField) exists

Question

suppose i need to print

select field1,max(field2) from table1

here by field1 mean value of field1, whcih is correpsonding to tuple in the value at max(field2).

How can this be done?

for Example

 |  49 | 2000 |
 |  50 | 2001 |
 |  63 | 2002 |
 |  79 | 2003 |

Here the maximum value is 79, which is of year 2003, but the above select statement willreturn

79 2000

How can i get the output like

79 2003

This is an example, and i am looking for a generic method.

I know I could first use order by clause and then limit 1, or I can write a subquery like Having abc >=All(some subquery),aint there any other method, which is less expensive?

http://dev.mysql.com/doc/refman/5.5/en//example-maximum-column-group-row.html — fancyPants, Mar 21 '14 at 09:08
possible duplicate of [Selecting all corresponding fields using MAX and GROUP BY](http://stackoverflow.com/questions/1305056/selecting-all-corresponding-fields-using-max-and-group-by) — fancyPants, Mar 21 '14 at 09:10

score 1 · Answer 1 · answered Mar 21 '14 at 09:14

1

Try This,

;With Cte as 
(
select max(F1)  mf from F
)
select f1,f2 from CTE 
inner join F on mf=f1

answered Mar 21 '14 at 09:14

AK47

3,707
3
17
36

score 1 · Answer 2 · answered Mar 21 '14 at 09:14

1

Try like this

SELECT * FROM
(SELECT MAX(field1) AS fld from table1) S 
Inner JOIN table1 T ON S.fld = T.field1

answered Mar 21 '14 at 09:14

Vignesh Kumar A

27,863
13
63
115

score 1 · Accepted Answer · answered Mar 21 '14 at 09:15

I guess you are trying to find maximum value of a certain column based on some specific field value.There different ways of doing this. Try below query :-

SELECT article, dealer, price 
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

You can use above query for reference and make change in your query accordingly.

Hope this helps.

How to select a field corresponding to the tuple in which max(anotehrField) exists

3 Answers3