Nov 14, 2014
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:
And I want to add an "instance number" column that gives me the instance of that person based on an alphabetical sorting on Food...
(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?
Jun 23, 2011
The closest thing I can think of is Col Rank(), but it doesn't currently support BY variables or character columns.