sql lite getting row number and reset for each group

Question

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

score 2 · Accepted Answer · answered Oct 05 '18 at 08:01

2

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;

answered Oct 05 '18 at 08:01

Shawn

47,241
3
26
60

sorry late response, i'm using old version....so theres no way it can access row_number() window fuction... – yuri astoria Oct 16 '18 at 06:55

sql lite getting row number and reset for each group

1 Answers1