get only 1 row from payment table

Question

get only 1 row from payment table

Simon Edri 1

hi there ,
i have this query

Select 
C.CID,C.NID,C.FirstName,C.LastName,C.ID,
PM.AccountNum,PM.Balnce,PM.DateFrom,PM.DateTo,PM.MoveSum,PM.OpenDate,PM.ExportDate,PM.Rem,
PT.PayTypeName,
P.PayID, P.OpenDate
From Customers C  
LEFT JOIN PaymentsMoves PM ON C.CID = PM.CID
LEFT JOIN (SELECT TOP 1 PayID , OpenDate, PayDate, CID, PaySum FROM Payments GROUP BY PayID,OpenDate, PayDate, CID, PaySum) 
P ON P.CID = C.CID AND PM.CID IS NULL
LEFT JOIN PayType PT ON PT.PID = PM.MoveType

from the Payments table I want to get only the max row from each CID how can I get it ?
thanks ...

Yitzhak Khabinsky 26,586 Reputation points

2021-10-11T14:59:31.133+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,621 Reputation points

2021-10-15T02:48:33.113+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

1 answer

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-10-11T14:59:31.133+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,621 Reputation points

2021-10-15T02:48:33.113+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Answer 1

Hi @Simon Edri ，

Welcome to the microsoft TSQL Q&A forum!

Please try:

 Select   
 C.CID,C.NID,C.FirstName,C.LastName,C.ID,  
 PM.AccountNum,PM.Balnce,PM.DateFrom,PM.DateTo,PM.MoveSum,PM.OpenDate,PM.ExportDate,PM.Rem,  
 PT.PayTypeName,  
 P.PayID, P.OpenDate  
 From Customers C    
 LEFT JOIN PaymentsMoves PM ON C.CID = PM.CID  
 LEFT JOIN (SELECT * FROM (SELECT PayID , OpenDate, PayDate,CID, PaySum,   
            MAX(CID) OVER(PARTITION BY PayID) mm   
            FROM Payments) t  
            WHERE CID=mm) P  
 ON P.CID = C.CID AND PM.CID IS NULL  
 LEFT JOIN PayType PT ON PT.PID = PM.MoveType

If this does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result.So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

get only 1 row from payment table

1 answer

Your answer