increasing counter in select 1

[Overdoos]

Hi,

I do not think that what I'm asking for is possible (plain SQL hasn't got the capacity to do this, normaly) but I'll ask anyway because it is a nice 'brain-excersise'

What I would like is 1 select-statement (I do not care how many sub-selects and other trics) that would get me the 'startdate', the 'contract_number', and an increasing counter.

example table:
| startdate | contract | enddate |
| 15/08/1980 | 1 | |
| 12/09/1980 | 2 | 12/12/1980 |
| 02/11/1980 | 3 | |
| 02/01/1981 | 2 | 02/06/1981 |
| 31/03/1981 | 4 | 30/04/1981 |
| 01/05/1981 | 4 | 31/05/1981 |
| 01/11/1981 | 2 | |

what I would like to get:
| startdate | contract | counter |
| 15/08/1980 | 1 | 1 |
| 12/09/1980 | 2 | 1 |
| 02/11/1980 | 3 | 1 |
| 02/01/1981 | 2 | 2 |
| 31/03/1981 | 4 | 1 |
| 01/05/1981 | 4 | 2 |
| 01/11/1981 | 2 | 3 |

ordered by contract this would be:
| startdate | contract | counter |
| 15/08/1980 | 1 | 1 |
| 12/09/1980 | 2 | 1 |
| 02/01/1981 | 2 | 2 |
| 01/11/1981 | 2 | 3 |
| 02/11/1980 | 3 | 1 |
| 31/03/1981 | 4 | 1 |
| 01/05/1981 | 4 | 2 |

think you can pull this off?
All working solutions that where not previously posted will be rewarded with a star (of course).

 

[Overdoos]

Oh, one small remark... I do not want to use sequences. I consider setting up a sequence an action OUTSIDE a select, and I want ONE select-statement (subselects and other stuff allowed)

 

[johnherman]

Use ROW NUMBER, see example from Oracle documentation below:

ROW_NUMBER Example
SELECT channel_desc, calendar_month_desc,
TO_CHAR(TRUNC(SUM(amount_sold), -6), '9,999,999,999') SALES$,
ROW_NUMBER() OVER (ORDER BY TRUNC(SUM(amount_sold), -6) DESC)
AS ROW_NUMBER
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=times.time_id AND
sales.channel_id=channels.channel_id AND
times.calendar_month_desc IN ('2000-09', '2000-10')
GROUP BY channel_desc, calendar_month_desc;

CHANNEL_DESC CALENDAR SALES$ ROW_NUMBER
-------------------- -------- -------------- ----------
Direct Sales 2000-10 10,000,000 1
Direct Sales 2000-09 9,000,000 2
Internet 2000-09 6,000,000 3
Internet 2000-10 6,000,000 4
Catalog 2000-09 3,000,000 5
Catalog 2000-10 3,000,000 6
Partners 2000-09 2,000,000 7
Partners 2000-10 2,000,000 8
Tele Sales 2000-09 1,000,000 9
Tele Sales 2000-10 1,000,000 10



-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[Overdoos]

hmmm... I have never used row_number before (to be honest, I didn't know it existed in a standard oracle 8i). Anywayz, I get a 'function not enabled' on the DB I need to run the statement on so I won't be able to use it.

Star awarded anyway because it does seem like a sweet solution if it should work.