RowNumber() with Partition in MYSQL
i want the below output based on id-Foreign key
id | Name | rownumber 1 a 1 1 b 2 1 ads 3 2 dsfs 1 2 sadf 2 2 sdfsa 3 2 dfsfs 4 3 dsf 1 3 adsf 2 3 sdd 3
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I barely understood what you mean. There’s no
RowNumber() function in mysql, and partitioning has nothing to do with your request.
SELECT t.*, @cur:= IF(<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="523b366f123b36">[email protected]</a>, @cur+1, 1) AS RowNumber, @id := id FROM t CROSS JOIN (SELECT @id:=(SELECT MIN(id) FROM t), @cur:=0) AS init ORDER BY t.id
@Alma Du, @Chintu is talking about SQL Server where you can apply row_number + partition over specific field(s).
Imagine that you have a table – named ‘customerPurchasesHist’ – that stores customer’s purchases history:
| customerNr | purchaseItem | purchaseDatetime | | 123 | microwave | 2014-06-05 | | 123 | television | 2014-09-10 | | 123 | fridge | 2015-01-10 | | 1234 | sofa | 2015-01-10 | (....)
In SQL Server, if you need to get the last two purchases from each client on that table, all you have to do is:
SELECT * FROM ( SELECT h.*, ROW_NUMBER() OVER (PARTITION BY customerNr ORDER BY purchaseDatetime DESC) AS sequence FROM customerPurchasesHist h )T WHERE 1=1 AND seq <= 2 ;
The result will be:
| customerNr | purchaseItem | purchaseDatetime | seq | | 123 | fridge | 2015-01-10 | 1 | | 123 | television | 2014-09-10 | 2 | | 1234 | sofa | 2015-01-10 | 1 |