Solved

selecting only one record in inner join

Posted on 2016-07-21
3
77 Views
Last Modified: 2016-07-21
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
Comment
Question by:AZZA-KHAMEES
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41722825
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41722869
0
 

Author Comment

by:AZZA-KHAMEES
ID: 41722876
ohh Sorry Rayan
your are correct
the query is working fine
0

Featured Post

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question