Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

selecting only one record in inner join

Hi experts, i have a long SQL query with inner join, but it retrieve a duplicat record as one of the table contain more thatn one reocrd with different  data
i need to retrieve only the last record in the table
select a.*, b.Institution,b.graduationYear,b.GPA, c.Quali, d.Lastqualification
from Applicant as a inner join Qualification as b on a.CPRNo = b.CPRNo
inner join Qualimain as c on b.code = c.code
inner join Qualisec as d on b.code = d.code and b.codese = d.codese
order by a.CPRNo

Open in new window


i need to get only the last record entered in Qualification table
id         cprno       institue      graduationYesr
--------------------------------------------------------------
1          20222       test            2011
2          20222       test2          2013
--------------------------------------------------------------

for example, from the above table i need only the second record to be listed

any suggestion
0
AZZA-KHAMEES
Asked:
AZZA-KHAMEES
2 Solutions
 
Ryan ChongCommented:
try:
select a.id, a.cprno, a.Institution, a.graduationYear, a.GPA, a.Quali, a.Lastqualification
from
(
select a.*, b.Institution,b.graduationYear,b.GPA, c.Quali, d.Lastqualification,
row_number() over (partition by a.CPRNo order by b.graduationYear desc) idx
from Applicant as a inner join Qualification as b on a.CPRNo = b.CPRNo
inner join Qualimain as c on b.code = c.code
inner join Qualisec as d on b.code = d.code and b.codese = d.codese
) a
where a.idx = 1
order by a.CPRNo

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
AZZA-KHAMEESAuthor Commented:
ohh Sorry Rayan
your are correct
the query is working fine
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now