How to select last record of each user in SQL

Question

0.00/5 (No votes)

See more:

hello i have three table in my database and i want to select the last record of each user by userid and packID
suppose my three table are as follow table1(UserID, name,contact)
table2(PackID, Packname)
table3(PayID,UserID,PackID)
here i want to retrive the last payID of each user if user has selected the same package two time then i want the record two time for example if table3 value in first row is (1,1,1) and in second row value are (2,1,1) then i want both this two record. but in my query i have group by them with userID that why it will just show the last record how can i get the last record by group them with userID and PackID

please help me

What I have tried:

i have used query is follow
select t1.UserID,t2.payID from table1 as t1
inner join (select max(PayID) as payID,max(UserID) as UserID from table3 group by UserID) as t2
on t1.UserID=t2.userID

Posted 25-Oct-16 19:25pm

AZHAR SAYYAD

Updated 26-Oct-16 7:45am

Add a Solution

Comments

Suvendu Shekhar Giri 26-Oct-16 1:44am

How to decide that user has selected same pakages 2 times?
Logic not clear. Share some sample input and output in tabular form which may help in understanding your issue.

2 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)

Ali Majed HA · Answer 1 · 2016-10-25T20:31:00

Solution 1

hello
if you are using sql query, you can
1) OredrByAsc(* fieldName), to show the last record, firs
2) Select top(1) to select the firs record, infact the last one

if you are using Entity Framework:
you can use .LastOrDefault(); to retrieve last one

hope it works

Posted 25-Oct-16 20:31pm

Ali Majed HA

Rahul_Biswas · Answer 2 · 2016-10-26T07:44:00

Try this:

SQL

;WITH cte1
AS (
	SELECT PAYID
		,USERID
		,PACKID
		,RANK() OVER (
			PARTITION BY USERID ORDER BY PAYID DESC
			) AS Rnk
	FROM table3
	)
	,cte2
AS (
	SELECT PAYID
		,USERID
		,PACKID
	FROM cte1
	WHERE Rnk = 1
	)
	
	SELECT t3.PAYID
		,t3.USERID
		,t3.PACKID
	FROM table3 t3
	INNER JOIN cte2 ON t3.PAYID <= cte2.PAYID
		AND t3.USERID = cte2.USERID
		AND t3.PACKID = cte2.PACKID

I made some assumptions:
1. PAYID will increase as more and more records get added. In other words, the greater the PAYID, the more recent it is. Your usage of MAX(PAYID) seems to suggest my assumption is correct.
2. You need all repetitions of a USERID, PACKID combo if those repetitions match the USERID, PACKID combo corresponding to the latest PAYID for that USER. So, there might be more than 2 repetitions. You will get all repetitions in my query.