How to get RowNumber() with Partition in MYSQL

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

Answers:

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.

Method 1

I barely understood what you mean. There’s no RowNumber() function in mysql, and partitioning has nothing to do with your request.

It’s:

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

Method 2

@Alma Du, @Chintu is talking about SQL Server where you can apply row_number + partition over specific field(s).

Practical Example:
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  |

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0