Forum Discussion

dipanjan_pan's avatar
dipanjan_pan
New Contributor
5 years ago

Scenario to achived rownumber function in sql

How can I achieve rownum() funtion in Snaplogic?

I have a input data
Column 1
a
b
c
a
a
need output
Column1 Column2
a 1
b 1
c 1
a 2
a 3

How can I achieve this?

8 Replies

  • alchemiz's avatar
    alchemiz
    Contributor III

    Hi Dipanjan,

    You can use the string method charCodeAt

    e,g

    ($Column1.toLowerCase().charCodeAt(0) - 97) + 1

    • alchemiz's avatar
      alchemiz
      Contributor III

      My bad, I thought column2 is base from the character from column1 🙂

  • I’m not understanding your example. If Column 2 is supposed to be your rownum() value, why wouldn’t the values be 1, 2, 3, 4, 5 instead of 1, 1, 1, 2, 3?

    • dipanjan_pan's avatar
      dipanjan_pan
      New Contributor

      rownum based on some partition by column. In this case I want the output based on column 1. thats why it is 1,1,1,2,3

      • ptaylor's avatar
        ptaylor
        Employee

        Sorry, can you please explain more specifically what you mean by that? What is the partitioning rule?

  • The expression snap.in.totalCount might be what you’re looking for, but I’m really not sure based on your example.