Select with Maximum value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select with Maximum value

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2011-09-21 : 04:22:28

Hi
I have two tables

Applicant (ApplicantId, Name)

Applicant
---------
AP1 Suniyas
AP2 Arun
AP3 Sureka

QualificationDetail(Qid,Qualification,OrderNo,ApplicantId)
Qualification
-------------
Q01 Bcom 1 AP1
Q02 MCom 2 AP1
Q03 CIMA 3 AP1
Q04 BS 1 AP2
Q05 MS 2 AP2
Q06 BTech 1 AP3
Q07 MTech 2 AP3
Q08 PhD 3 AP3

I would like to display the details of each applicant only with highest qualification(based on orderno column)

Required output
---------------
Sno AppName HighestQualification
--------------------------------
1 Suniyas CIMA
2 Arun MS
3 Sureka PhD
------------------------

Thanks for your help.

Rgds
SSM

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-09-21 : 04:37:11

[code]
select q.ApplicantId,a.Name,q.Qualification
from QualificationDetail q
inner join (select ApplicantId,MAX(OrderNo) MaxQualif
from QualificationDetail
group by ApplicantId)Der on Der.ApplicantId=q.ApplicantId and Der.MaxQualif=q.OrderNo
inner join Applicant a on a.ApplicantId=q.ApplicantId

[/code]

--Ranjit

sureshsmanian
Starting Member

31 Posts

Posted - 2011-09-21 : 05:09:42

@Ranjit
Thanks for ur help. Its working fine.
Rgds
SSM

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-09-21 : 05:40:23

Welcome suresh!

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources