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