How can I get the penultimate records of a group in MySQL?
I have a table called "table_deposit" with 4 columns (id, user, date_deposit, amount_deposited), and I want to group by users column and select the penultimate record of each user.
id|user|deposit_date|deposited_amount
1 | user1 | 01/01/19 | 1000
2 | user2 | 01/01/19 | 500
3 | user3 | 01/01/19 | 400
4 | user2 | 02/02/19 | 3500
5 | user1 | 02/02/19 | 100
6 | user3 | 02/02/19 | 50
as a result I want something like this, grouping by users and selecting the penultimate records and discarding the last records.
I imagine that filtering is done by id or by date. selecting the last one and subtracting 1. example:
id|usuario|fecha_deposito|monto_depositado
1 | user1 | 01/01/19 | 1000
2 | user2 | 01/01/19 | 500
3 | user3 | 01/01/19 | 400
I placed 6 example records but there are many more. What I want is to group by user and select the penultimate record of each user and show it in a query.
Example: if there are 10 registered users with n number of deposits, then show me the 10 users with the penultimate deposit made by each user.
I have already tried LIMIT, but it does not give me the selection I am looking for because it only takes the penultimate but from a single user and what I need is from all the users that are grouped with group by users.