Alternate Solution to Self Join

Database

Alternate Solution to Self Join

Dear All, Need help with below situation....

Data

id        date

100   6/6/2015

100   8/1/2015

100   9/30/2015

200   8/11/2015

300   7/18/2015

300   1/10/2015

300   3/5/2015

300   6/15/2015

Result

id       date          count

100  9/30/2015       2

100  8/1/2015         1

100  6/6/2015         0

200   8/11/2015      0

300   7/18/2015      3

300   6/15/2015      2

300   3/5/2015        1

300   1/10/2015      0

Requirement : to count the dates that are older than the date in the current row. I can write the query using a self join. But like to know if there is any alternate.

Appreciate your help on my request.

2 REPLIES
N/A

Re: Alternate Solution to Self Join

check the row_number() function in the SQL manual

Re: Alternate Solution to Self Join

SELECT ID,DATE,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY DATE)-1 AS COUNT FROM TBL

Can u try this