4
Good morning guys, I would like to know how to do the following SELECT in mysql:
Having the following table, I need to select the last 3 purchases of each Name:
From now on, thank you
1 answer
2
Rone,
an option is this:
SELECT A.* FROM COMPRAS A
WHERE (SELECT COUNT(*) FROM COMPRAS B WHERE B.NOME = A.NOME AND B.DATA >= A.DATA) <= 3
ORDER BY NOME, DATA;
-
It worked perfectly. I also have the following situation:
-
The select would be with a limit of 20 results, but if the Name appears more than 3 times it eliminates the excess times, giving opportunity for other names to appear in this list of 20. Also ordered by date.
-
1
If I understand well, I believe it is only necessary to put the LIMIT 20 at the end of the query ordered only by date. Like this: SELECT A.* FROM PURCHASES A WHERE (SELECT COUNT(*) FROM PURCHASES B WHERE B.NAME = A.NAME AND B.DATA >= A.DATA) <= 3 ORDER BY DATA LIMIT 20;
-
I put it like this: ORDER BY data DESC LIMIT 20, then it was perfect, Thank you very much Davy for the help...
-
That’s right Rone!
Browser other questions tagged mysql select-sql
You are not signed in. Login or sign up in order to post.
select * from table group by nome order by data asc limit 3– R.Santos
This way it takes the last 3 values but 1 of each Name
– Rone Sigismundo
Rone, you have a unique ID on each record?
– Fernando Mertins
I’m sorry I didn’t inform you, but I do.
– Rone Sigismundo