Second posting here, still in learning stages. I have adopted code and came across the following. I know it works, but I want to understand what it is doing.
ROW_NUMBER() Over (PARTITION BY A.TID, D.PIN, TYr
ORDER BY A.TID, D.PIN, TYr, D.Amt) Seq_no
Note: A. and D. are aliases created by AS clauses within JOINs.
Values for A.TID are 0001, 0002, 0003, 0004, 0005
D.PIN are 1111, 2222, 3333, 3333, 5555, 4444, 3333, 1111, 2222, 2222
TYr are 2009, 2008, 2009, 2010, 2009, 2009, 2008
D.Amt varies greatly
Seq_no is not known at present
Thanks in advance and God Bless,
ROW_NUMBER generates the allocates a sequence number to the data set.
In your case, for each unique combination of A.TID, D.PIN, Tyr (columns in the PARTITION BY clause) will have a unique sequence number and the allocation of that sequence number will be done according the sorting order defined by D.PIN, Tyr, D.Amt (columns in the ORDER BY clause).
Seq_no is the allias of the columns returned by ROW_NUMBER function.