INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

Hi

CODE --> MySQL

select case when @prev != term.term then (@prev := term.term) & (@row := @row + 1) & 0 else 0 end | @row row_number, term.term, person.person_id
FROM
term,
person,
(select @row := 0, @prev := ') var
WHERE
term.term=person.term 

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

Feherke.
feherke.ga

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

(OP)
Hey Feherke,

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

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

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

Hi

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

Feherke.
feherke.ga

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close