0

Let's say I have this table.

users|amount
------------
User1|  500
User1|  400
User1|  300
User1|  200
User1|  100
User2|  500
User2|  400
User2|  300
User2|  200
User3|  300
User3|  200

I want to display at most 3 transaction for each user. The result given in the example should be this.

users|amount
------------
User1|  500
User1|  400
User1|  300
User2|  500
User2|  400
User2|  300
User3|  300
User3|  200

Is there a way to do this in SQL (MySQL)? What steps should I do?

Mr A
  • 1,345
  • 4
  • 22
  • 51

2 Answers2

1

you can try like this

SELECT
    users, amount
FROM (SELECT
    users, amount,
    ROW_NUMBER() OVER (PARTITION BY users ORDER BY amount DESC) AS rn
FROM yourtable) AS a
WHERE a.rn <= 3
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
0

You can use variables for this:

SELECT users, amount
FROM (
  SELECT users, amount,
         @grp := IF(@usr = users, @grp + 1,
                    IF(@usr := users, 1, 1)) AS grp
  FROM mytable
  CROSS JOIN (SELECT @grp := 0, @usr = '') AS vars
  ORDER BY users, amount DESC) AS t
WHERE t.grp <= 3

@grp variables enumerates records within each users partition. The outer query uses this variable in order to retrieve at most 3 records per user.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98