How to fetch Max Value according to foreign key?
Do you need to fetch the max value in a table based on the foreign key in it? This article is based on the SQL subqueries which will let you choose the max value from a table according to the foreign key.
There came a problem an year ago while we working, where we had to find the max value in a table on the basis of the foreign key present in it. That time we solved this issue with help of while loop. But again this issue arised and this time we decided to solve it with the help of Query and sub queries. SQL Query to find the max value according to the foreign key
Let me describe this query a little before we actually look into it. The 2 table names I have used are Test1 and Test1Dt1.
Here we would first fetch the max number according to Test1ID from the Test1Dt1 table. After that use it in sub query to fetch all data from the table from where we will fetch Max value. After that again use sub query and use Inner join from First table.
I will first create 2 tables Test1 and Test1Dt1 and insert data in it accordingly. The SQL statement for creating and inserting data in these tables are as follows:
create table Test1(ID int,Name varchar(150))
insert into Test1 values(1,'Rajat')
insert into Test1 values(2,'kishan')
insert into Test1 values(3,'Raman')
create table Test1Dtl(DtlID int,Test1ID int,Productname varchar(150))
insert into Test1Dtl values(1,1,'AAAAA')
insert into Test1Dtl values(2,1,'BBBBB')
insert into Test1Dtl values(3,2,'CCCCC')
insert into Test1Dtl values(4,1,'DDDDD')
insert into Test1Dtl values(5,2,'EEEEE')
insert into Test1Dtl values(6,3,'FFFFF')
Now I would tell you the actual query which we will use to fetch the max value in the table Test1Dt1 based on the foreign key Test1ID.
select * from (
select * from Test1Dtl where DtlID in(
select MAX(DtlID) from Test1Dtl
group by Test1ID
)) as A
inner join Test1 on Test1.ID=A.Test1ID
The output received on exectuing the above query will be :
DtlID Test1ID ProductName ID Name
4 1 DDDDD 1 Om
5 2 EEEEE 2 kishan
6 3 FFFFF 3 Raman