×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Enumerate (count) records in groups

Enumerate (count) records in groups

Enumerate (count) records in groups

(OP)
I have a table that stores (among other things) district number and order number.  Each order can have multiple transaction dates (each time it has been touched).  What I need is a SELECT statement that can give me an ascending sequence for each record grouped by district and order.

So if the columns are: district, order, tr_date

And the data might look like this:
0021, 00001111, 01/12/09
0021, 00001111, 01/15/09
0021, 00001111, 01/23/09
0021, 00001213, 01/14/09
0021, 00001213, 01/16/09
0018, 00001019, 12/27/09

Would need to look like this:
0021, 00001111, 01/12/09, 1
0021, 00001111, 01/15/09, 2
0021, 00001111, 01/23/09, 3
0021, 00001213, 01/14/09, 1
0021, 00001213, 01/16/09, 2
0018, 00001019, 12/27/09, 1

Any ideas?

RE: Enumerate (count) records in groups

(OP)
I think I figured it out:

CODE

select
     t1.district,
     t1.order_no,
     t1.tr_date,
     sum(case when t2.tr_date <= t1.tr_date then 1 else 0 end) as tr_seq
from
     tbl_example t1
     inner join
     (select
          district,
          order_no,
          tr_date
     from
          tbl_example) t2
          on
          t1.district = t2.district
          and
          t1.order_no = t2.order_no
group by
     t1.district,
     t1.order_no,
     t1.tr_date

It seems to work, at least.

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!

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