Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2010-11-09
2
Medium Priority
?
716 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:tia_kamakshi
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34099095

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
 

Author Closing Comment

by:tia_kamakshi
ID: 34103138
Great

Many Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 19 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question