Getting top 50 transaction for each customer and getting transaction count no

Hi,

I am writing a query in Mysql. I need top 50 transaction for each customer done by customer (ORDER BY TRANSACTION.TRANSACTIONDATE)

Also, can I get the transaction count no for each transaction. I mean first transaction placed should have count 1, next one should have count 2 etc

I need to bring this information in my select query.

I am getting all of these transaction details in bulk by excel file and admin can upload excel file as many times as they wanted,
so Its hard to take column 'count' in transaction table.

Please suggest me to bring this information in select query by using function or anything else

Thanks & Regards
SELECT 
	TRANSATION.CLIENTID, TRANSACTION.PRODUCTISINCODE, TRANSACTION.TRANSACTIONDATE,TRANSATION.ISAPPROVED
FROM 
	TRANSACION
WHERE 
	TRANSACTION.ISDELETED=0
GROUP BY 
	TRANSATION.CLIENTID
ORDER BY 
	TRANSACTION.TRANSACTIONDATE

Open in new window

tia_kamakshiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:

SELECT CLIENTID, PRODUCTISINCODE, TRANSACTIONDATE, ISAPPROVED
FROM
(
SELECT 
	T.CLIENTID, T.PRODUCTISINCODE, T.TRANSACTIONDATE, T.ISAPPROVED,
	@r:=case when @c=T.CLIENTID then @r+1 else 1 end r,
	@c:=T.CLIENTID
FROM 
   (select @c:=null,@r:=0) n, TRANSACTION T
WHERE 
	T.ISDELETED=0
ORDER BY 
	T.CLIENTID, T.TRANSACTIONDATE
) X
WHERE r<=50
ORDER BY
	CLIENTID, TRANSACTIONDATE

Open in new window

0
 
tia_kamakshiAuthor Commented:
Great

Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.