Would someone please explain ROW_NUMBER

Database
TDW
N/A

Would someone please explain ROW_NUMBER

Hi,

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,

Genesius

2 REPLIES

Re: Would someone please explain ROW_NUMBER

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.

TDW
N/A

Re: Would someone please explain ROW_NUMBER

Thanks for your hwlp Qaisar.

God Bless,

Genesius