<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />In a JMP table, what is the best-known method for doing the equivalent of ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] ORDER BY ...) that we can do in SQL (such as Transact-SQL)?
For the T-SQL version see: https://msdn.microsoft.com/en-us/library/ms186734.aspx
If I have this example table:
| Name | Food |
|---|
| Alice | Toast |
| Bob | Orange |
| Alice | Apple |
| Charlie | Pizza |
| Bob | Tomato |
And I want to add an "instance number" column that gives me the instance of that person based on an alphabetical sorting on Food...
Name
| Food | Instance
|
|---|
| Alice | Toast | 2 |
| Bob | Orange | 1 |
| Alice | Apple | 1 |
| Charlie | Pizza | 1 |
| Bob | Tomato | 2 |
(Order of the output table doesn't matter.)
A method I found so far is to sort the input table by (Name, Food) and add a formula column:
If(Row() == 1, 1, If(Lag(:Name, 1) == :Name, Lag(:Instance, 1) + 1, 1))
Is this the easiest way to mimic the ROW_NUMBER SQL function, or does JMP offer a more convenient way?
The closest thing I can think of is Col Rank(), but it doesn't currently support BY variables or character columns.
