0

I have a table that contains the ids of receipts, each associated with a the id of a client. I have been trying for a while now, to get the biggest id and the second biggest id of the receits associated with each client.

I have tried the following:

select max(id), client
from table
where deleted = 0
group by cliente 
order by id;

but to no avail. My idea was to get the second biggest by taking first the biggest and then excluding the id of the biggest from a query that would take all of the receipts, but i cant even get the query that takes the biggest receipts to work.

Any suggestions?

Rick James
  • 135,179
  • 13
  • 127
  • 222
joao pereira
  • 55
  • 5
  • This is a common task. There are a lot of examples in the archives for "top x rows per group" such as https://stackoverflow.com/a/59416397/8895292 i.e. Use a cte with `ROW_NUMBER() OVER(PARTITION BY Client ORDER BY ID Desc) AS RowNum` and then filter on `where RowNum <= 2` – SOS Mar 08 '22 at 16:41
  • 1
    @SOS i had searched the problem before and i have tried all of the proposed solutions but they dont work. Over() doesnt work with my version of sql, the solutions with "select max(id) ... group by client" dont work. but the solution posted by "user2674659" in the page that you sent worked, thank you. – joao pereira Mar 08 '22 at 17:18
  • 1
    Glad you found something that works for you! For future questions, be sure to include your MySQL version, since most people will wrongly assume you're using MySQL 8.x (like I did :-) – SOS Mar 08 '22 at 17:29
  • See the tag I added. – Rick James Mar 16 '22 at 02:54

0 Answers0