Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
I a table "person" and need a query for those personName who's
 
1.salary is maximum
 
2.cityCode is 12
 
3. age is 32
 

 
suppose- there are 3 person with name RAM .
 
AND 3 with name sunil
 
and 3 person with name rahul....
 

and i need the only those record who contain the maximum salary
 
means ram with maximum salary , sunil with maximum salary and rahul with maximum salary
Posted 9-Jul-13 19:50pm
Edited 9-Jul-13 20:31pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If you are asking for three queries....
Select top(1) PersonName from person Order by Salary Desc -- maximum salary
Select PersonName from person where citycode=12  -- CityCode=12
select PersonName from Person where age=32  -- age=32
 
if you are asking for name of the person earning highest salary in citycode 12 and age 32. Then the query is:
Select top(1) PersonName from person where citycode=12 and age=32 order by salary Desc
...
 
if this is not what u required.. use Improve Question widget....
 
As per your requirement u can use the multiQuery written by Damian S or
Select p1.PersonName from person p1 
inner join (Select Max(salary) as maxsalary from person where Citycode=12 and age=32)p2 on p1.salary=p2.maxsalary Where p1.CityCode=12 and p1.age=32
 
But if you want max salary grouped on person you can use:
Select PersonName, max(Salary) as Salary from person where age=32 and Citycode=12 Group by PersonName
  Permalink  
v5
Comments
_Damian S_ at 10-Jul-13 1:12am
   
Very nice solution using select top(1)... have a 5 on me!!
Raja Sekhar S at 10-Jul-13 1:14am
   
Thank You _Damian S_...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

This will require a multipart query. You need one part to find the maximum salary for a given citycode and age, and one part to return the name... like this:
 
select personName 
from person 
where salary = (select max(salary) from person where citycode = 12 and age = 32) 
and citycode = 12 
and age = 32
  Permalink  
Comments
Raja Sekhar S at 10-Jul-13 1:16am
   
Good one... +5..!
_Damian S_ at 10-Jul-13 1:18am
   
Two different solutions for the OP to choose from!!
Raja Sekhar S at 10-Jul-13 1:28am
   
Yep...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Try this
 
select  PersonName, MAX(Salary)
from    PERSON
where   cityCode = 12
and Age = 32
group by PersonName
  Permalink  
Comments
siddharth629 at 10-Jul-13 5:01am
   
thanks sir.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 Maciej Los 210
4 PIEBALDconsult 184
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,665


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 10 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100