Search code examples
sqliterow-number

sql lite getting row number and reset for each group

already did searching for the topic , and got thread and try to modified to my needed but it end with stuck.

From this link Getting row number for query

i try to get rownumber reset for each group...

example : LetterNumber | DateLetter | ToLetter | Row AA-010-2018 | 2018-09-01 | Jon | 1 AA-010-2018 | 2018-09-01 | Dian | 2 AA-010-2018 | 2018-09-01 | Jajang | 3 BB-011-2018 | 2018-09-01 | Julaeha | 4 BB-011-2018 | 2018-09-01 | Endang | 5 BB-011-2018 | 2018-09-01 | Sutisna | 6

and i want to make it like this : LetterNumber | DateLetter | ToLetter | Row AA-010-2018 | 2018-09-01 | Jon | 1 AA-010-2018 | 2018-09-01 | Dian | 2 AA-010-2018 | 2018-09-01 | Jajang | 3 BB-011-2018 | 2018-09-01 | Julaeha | 1 BB-011-2018 | 2018-09-01 | Endang | 2 BB-011-2018 | 2018-09-01 | Sutisna | 3

how can i achieve it in sql lite ?

thank u

Solution

  • If you're using Sqlite 3.25 or better, you can use the row_number() window function:

    SELECT LetterNumber
     , DateLetter
     , ToLetter
     , row_number() OVER (PARTITION BY LetterNumber) AS Row
FROM your_table
ORDER BY LetterNumber;