Select statement where I can add a row number to reset at a group

[kernal]

Select statement where I can add a row number to reset at a group. Group would be the term:

TERM table

term
1078
1084
1086
1088

PERSON table

term person_id
1078 1
1078 2
1084 1
1084 2
1086 1
1088 1

SELECT STATEMENT

select row_number, term.term, person.person_id
FROM
term,
person
WHERE
term.term=person.term

RESULTS WANTED

row_number term person_id
1 1078 1
1 1078 2
2 1084 1
2 1084 2
3 1086 1
4 1088 1

Help is appreciated.

THANKS

 

[feherke]

Hi

[b]select[/b] [highlight]case when @prev [teal]!=[/teal] term[teal].[/teal]term then [teal]([/teal]@prev [teal]:=[/teal] term[teal].[/teal]term[teal]) & ([/teal]@row [teal]:=[/teal] @row [teal]+[/teal] [purple]1[/purple][teal]) &[/teal] [purple]0[/purple] else [purple]0[/purple] end [teal]|[/teal] @row[/highlight] row_number[teal],[/teal] term[teal].[/teal]term[teal],[/teal] person[teal].[/teal]person_id
[b]FROM[/b]
term[teal],[/teal]
person[highlight][teal],[/teal]
[teal]([/teal][b]select[/b] @row [teal]:=[/teal] [purple]0[/purple][teal],[/teal] @prev [teal]:=[/teal] [i][green]'[/green][/i][teal])[/teal] var[/highlight]
[b]WHERE[/b]
term[teal].[/teal]term[teal]=[/teal]person[teal].[/teal]term

Note that this solution heavily depends on the order in which records are returned. You may want to add an [tt]order by term.term[/tt] clause to your [tt]select[/tt].

Feherke.
feherke.ga

 

[kernal]

Hey Feherke,

Thanks for helping me. It's giving me an syntax error on the following:

(select @row := 0, @prev := ') var

 

[feherke]

Hi

Doh. There should be of course two single quotes : [tt]@prev := ''[/tt]. But one of them seems to disappear during editing the post.

Feherke.
feherke.ga