Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Enumerate (count) records in groups

Status
Not open for further replies.

DemBones79

Programmer
Jun 12, 2008
7
US
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?
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top