?
Solved

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

Posted on 2010-11-09
2
Medium Priority
?
713 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

801 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