CODEWITHSUNDEEP
mysqlsql
Amrith Krishna
Amrith Krishna

Reputation: 2851

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

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?

Upvotes: 2

Views: 86

Answers (3)

Sukane
Sukane

Reputation: 2680

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.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try like this

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

Upvotes: 1

AK47
AK47

Reputation: 3807

Try This,

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

Upvotes: 1

Related Questions