Subscribe Bookmark RSS Feed

How to do "row-number-over"?

a01

Community Trekker

Joined:

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:

NameFood
AliceToast
BobOrange
AliceApple
CharliePizza
BobTomato

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
FoodInstance
AliceToast2
BobOrange1
AliceApple1
CharliePizza1
BobTomato2

(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?

1 REPLY
XanGregg

Staff

Joined:

Jun 23, 2011

The closest thing I can think of is Col Rank(), but it doesn't currently support BY variables or character columns.