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.
Something like this should work for you...
I'll explain, I didn't want to put it as a solution, because surely there is a better way. They are 2 queries.
SELECT COUNT(*) FROM TABLA
This returns us the number of records that the table has. Since you want the penultimate, we subtract 2.For example: if 36 comes out, it will remain in 34.
We save this number.
SELECT NOMBRE, CANTIDAD FROM TABLA LIMIT 34, 1
This query discards the last record.
I hope it helps you. Luck.
You must select the maximum
id
and group byusuario
: