how to select max id record through joining

Question

0.00/5 (No votes)

See more:

i have a procedure ........

SQL

alter PROCEDURE [dbo].sp_GatherCleintInfofoDeactivation  
(  
@Center_Id int,  
@Company_U_Id int,  
@CRT_Result int,  
@Loan_Proposal_Status int  
)  

AS  
BEGIN  

 SET NOCOUNT ON;  



SELECT     TargetInfo.m_name,TargetInfo.Client_Id, ClientCRTReportInfo.Client_Id as clientID,ClientCRTReportInfo.CRTId,ClientCRTReportInfo.SubGroup_Id,TempPaymentShceduleInfo.TempId   into #TempAllWeights
FROM         CenterInfo INNER JOIN  
                      ClientCRTReportInfo ON CenterInfo.Center_Id = ClientCRTReportInfo.Center_Id INNER JOIN  
                      TargetInfo ON CenterInfo.Center_Id = TargetInfo.Center_Id AND ClientCRTReportInfo.Client_Id = TargetInfo.Client_Id_Auto  
                      inner join TempPaymentShceduleInfo  on TargetInfo.Client_Id_Auto =TempPaymentShceduleInfo.Client_Id
WHERE     (CenterInfo.Center_Id = @Center_Id) AND (ClientCRTReportInfo.Company_U_Id = @Company_U_Id) and TempPaymentShceduleInfo.TempId=(select MAX(TempId) from TempPaymentShceduleInfo)

select * from #TempAllWeights






                     
END  
RETURN

here the line where i markeed ,
(i.e. TempPaymentShceduleInfo.TempId=(select MAX(TempId) from TempPaymentShceduleInfo))
i want to select the record with maximum temp_id in "TempPaymentShceduleInfo" for all clients individual, tempId is the primary key of "TempPaymentShceduleInfo". how can i solve this ...?pls rply ...thanks in advance..

Posted 30-May-13 19:21pm

GDdixit

Updated 30-May-13 19:36pm

v3

Add a Solution

Comments

sanchit patne 31-May-13 2:15am

can you provide details of tables you have used for mentioned procedure. i mean in what manner you insert data in respective tables. for example can u give us table details with raw data and output u want from that data, so it will help us.

GDdixit 31-May-13 4:45am

actually i am unable to give detail at this time , but i can explain u this in detail what i want ...... ..

i have three tables .....
1. ClientDetailTable(clientId as primary key )
2. PaymentDetailTable (paymentId as primary key ) (clientId as foreign key )
3. AddressDetailTable (AddId as primary key) (clientId as foreign key)

now i want to select all clients record which exist in "ClientDetailTable" and their addresses from

"AddressDetailTable " . i have no problem in this (record for each client exist only once in both "AddressDetailTable " and "ClientDetailTable" ) , but when i am trying to join corressponds record from "PaymentDetailTable " i have arise a problem . Actually in "PaymentDetailTable " record exist more than once (paymentId is set identity(1,1)) now i want to select record from "PaymentDetailTable " which has maximum "paymentId " for that client ....
and in this way i want to join these three tables ....pls give suggestion bro......

i hope u understand the problem , waiting for response ......

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

GDdixit · Answer 1 · 2013-05-30T22:44:00

actually i am unable to give detail at this time , but i can explain u this in detail what i want ...... ..

i have three tables .....
1. ClientDetailTable(clientId as primary key )
2. PaymentDetailTable (paymentId as primary key ) (clientId as foreign key )
3. AddressDetailTable (AddId as primary key) (clientId as foreign key)

now i want to select all clients record which exist in "ClientDetailTable" and their addresses from

"AddressDetailTable " . i have no problem in this (record for each client exist only once in both "AddressDetailTable " and "ClientDetailTable" ) , but when i am trying to join corressponds record from "PaymentDetailTable " i have arise a problem . Actually in "PaymentDetailTable " record exist more than once (paymentId is set identity(1,1)) now i want to select record from "PaymentDetailTable " which has maximum "paymentId " for that client ....
and in this way i want to join these three tables ....pls give suggestion bro......

i hope u understand the problem , waiting for response ......

Makwana Kishor · Answer 2 · 2013-05-30T23:38:00

SELECT
t.*
FROM
TableX AS t
JOIN
( SELECT DISTINCT
title --- what you want to Group By
FROM TableX
) AS dt
ON t.PK = --- the Primary Key of the table
( SELECT tt.PK
FROM TableX AS tt
WHERE tt.title = dt.title
ORDER BY id ASC --- (or DESC) what you want to Order By
LIMIT 1
)

Kishor Makwana
Software Engineer
Insight Softech
www.insightsoftech.com

sanchit patne · Answer 3 · 2013-06-03T19:26:00

SQL

WITH T AS (select cd.*,ad.*,pd.*,ROW_NUMBER() over (partition by pd.clientID order by pd.paymentID desc) rowNumber
from ClientDetailTable cd inner join AddressDetailTable ad on cd.clientID = ad.clientID
inner join PaymentDetailTable pd on cd.clientID = pd.clientID)

select * from T where rowNumber = 1

In this query, you just need to replace table name with your table name.
Please let me know if you get the right solution from this query.

how to select max id record through joining

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0