2

I have transaction table in which i am storing transaction user wise. i need last n transaction userwise. What is best way to find last n transaction group by userId. OR to store last n transaction in other meta table is good way. Plz help me, because I have to send to client last 10 transaction userwise.

What is best solution. I am using mysql database.

How can we create view of last 10 transaction of each user...is it possible??

Bhupendra Pandey
  • 348
  • 3
  • 16
  • The best solution would have you showing us your table layout and some sample data along with what you expect as the output. – Jay Blanchard Nov 13 '14 at 20:07
  • Have a look at http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ and section *Select the top N rows from each group* – rabudde Nov 13 '14 at 20:10
  • suppose user_id and amount...two column in database. i have to find or give output to client last 10 trasanction of every user. what kind of solution you will suggest – Bhupendra Pandey Nov 13 '14 at 20:10
  • http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group – Marc B Nov 13 '14 at 21:12

1 Answers1

0

you can do it using variables.

The row_number is assigned for each user transaction, and results are ordered by transaction date, so we can get only first 10 rows for each user.

select * from (
select *,
  @num := if(@user_id= user_id, @num + 1, 1) as row_number,
  @user_id := user_id as dummy
from transactions,
(select @num := 0, @user_id:= '') d
order by user_id, transDate desc )T
where row_number <= 10;
radar
  • 13,270
  • 2
  • 25
  • 33