How to load last record in SQL

Question

0.00/5 (No votes)

See more:

i have Four table

SQL

tblUser(UserID PK,name)
tblPackage (PackageID  pk, PackageName)
tblPayment(PaymentID Pk,UserID Fk, PackageID FK,Payingdate,PaidAmount,RemainingBalance)
tblsubscription(subID pk, userID, packageID,PackageExpireDate, PackStartDate)

Here suppose in my tblpaymet i am adding the value

SQL

(1,1,1,2016-11-2,2000,8000),
(2,1,1,2016-11-3,3000,7000),
(3,1,1,2016-11-4,2000,8000)

and if same user take another package

SQL

(4,1,2,2016-11-1,5000,7000),
(5,1,2,2016-11-2,5000,7000),
(6,1,2,2016-11-3,5000,7000),

in this both statement i want to retrive the last record of each user by differentiate them packageID means in my example i want to retrive only paymentid(3,6) but using this query i am getting all record. so please help me to get the last record of each user

What I have tried:

SQL

select  users.UserID, users.Name, users.Contact, users.photos, pay.PaymentID, pay.Paying_date, pay.Payble, pay.Discount, pay.Total_Paid,p ay.Balance_Remain, pay.Payment_Mode, pay.Payment_Type, pay.Payment_Describtion, pack.PackageID, pack.Package_Name, pack.Package_Duration, pack.Package_Amount, sub.Package_Expire_Date, sub.Package_StartDate from tblUser as users 
	inner join tblPayment as pay 
	on users.UserID=pay.UserId
	left outer join tblPackages as pack
	on pack.PackageID = pay.PackageId
	inner join tblSubscription as sub 
	on users.UserID = sub.UID

Posted 3-Nov-16 1:38am

AZHAR SAYYAD

Updated 3-Nov-16 4:36am

phil.o

v2

Add a Solution

Comments

Suvendu Shekhar Giri 3-Nov-16 9:54am

..and what have you tried to get the last records?

Clue:

Try GROUP BY with HAVING
or ROW_NUMBER()

1 solution

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)

Richard Deeming · Accepted Answer · 2016-11-03T04:36:00

Something like this should work:

SQL

WITH ctePayment As
(
    SELECT
        PaymentID,
        UserID,
        PackageID,
        Paying_date, 
        Payble,
        Discount, 
        Total_Paid,
        Balance_Remain, 
        Payment_Mode, 
        Payment_Type, 
        Payment_Description, 
        ROW_NUMBER() OVER (PARTITION BY UserID, PackageID ORDER BY Paying_date DESC) As RN
    FROM
        tblPayment
)
SELECT 
    users.UserID, 
    users.Name, 
    users.Contact, 
    users.photos, 
    pay.PaymentID, 
    pay.Paying_date, 
    pay.Payble,
    pay.Discount, 
    pay.Total_Paid,
    pay.Balance_Remain, 
    pay.Payment_Mode, 
    pay.Payment_Type, 
    pay.Payment_Description, 
    pack.PackageID, 
    pack.Package_Name,
    pack.Package_Duration, 
    pack.Package_Amount, 
    sub.Package_Expire_Date, 
    sub.Package_StartDate 
FROM
    tblUser As users 
    INNER JOIN ctePayment As pay 
    ON pay.UserId = users.UserID 
    And pay.RN = 1
    INNER JOIN tblSubscription as sub 
    ON sub.UID = users.UserID
    And sub.PackageID = pay.PackageUD
    INNER JOIN tblPackages As pack
    ON pack.PackageID = pay.PackageID
;