Solved

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

Posted on 2010-11-09
2
705 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 500 total points
Comment Utility

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
Comment Utility
Great

Many Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now