All Forums Database
chakdom 8 posts Joined 07/08
12 Nov 2015
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.

ulrich 816 posts Joined 09/09
13 Nov 2015

check the row_number() function in the SQL manual

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

manib0907 61 posts Joined 04/15
14 Nov 2015

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

 

Cheers,
Mani

You must sign in to leave a comment.