Row_Number with OVER/ORDER (V5R4)

[FaStOnE]

Here is an example of using ROW_NUMBER with the OVER and ORDER BY in v5r4

SELECT row_number() 
OVER( partition by opaccount order by Dec( OPINVDAT ), 8, 0 ) as RowNo,
opaccount, opinvdat                                             
FROM Lib/Table
WHERE OPBALOPN = 'O' ; 

--The above example was provided by one of our Top Notch programmers and Code400 members (kast1) here in Phoenix. Thanks Lance!

[jamief]

Re: Row_Number with OVER/ORDER (V5R4)

Okay - can you explain this to us that have no idea of OVER and partition and ....
what exactly (in words) is this bad boy doing?

thanks for sharing
whatz the temperature there like 180?????

jamie

[FaStOnE]

Re: Row_Number with OVER/ORDER (V5R4)

This will order a result set by the grouping specified (the partition by clause).

Does that help?

And heck no .. it's only 93 right now in Paradise!

[jamief]

Re: Row_Number with OVER/ORDER (V5R4)

found this on http://www.databasejournal.com/




create the table

SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F') 

ROW_NUMBER Function

The first ranking function I will discuss is the ROW_NUMBER function.
This function returns a sequential number starting at 1 for each row
or grouping within your result set. The ROW_NUMBER function has
the following calling syntax:

ROW_NUMBER ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> ) 

Where the:

“” is a column or set of columns used to
determine the grouping in which the ROW_NUMBER function
applies sequential numbering.

” is a column or set of columns used to
order the result set within the grouping (partition).

To demonstrate how to use the ROW_NUMBER function,
my first example below will sequentially number all the rows
in my Person table, and order them by Age.

SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age], 
       FirstName,
       Age 
  FROM Person 

Here is the result set for the above T-SQL code:


Row Number by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2 Doris 6
3 George 6
4 Mary 11
5 Sherry 11
6 Sam 17
7 Ted 23
8 Marty 23
9 Sue 29
10 Frank 38
11 John 40

Here you can see I have sequentially numbered all my
Person table rows starting from 1, and the result set is
ordered by the Age column. This ordering was accomplished
by placing the “ORDER BY Age” criteria in the ORDER BY
clause of the ROW_NUMBER function.

Suppose you do not want your result set to be ordered,
but you want to simply just sequentially number each row.
The ROW_NUMBER function requires an ORDER BY clause, so
something is needed in this clause. In the following query I
specified “SELECT 1” for the ORDER BY clauses, doing this
just returned my rows physically how they where stored,
and sequentially numbered them starting from 1:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set], 
       FirstName, 
       Age 
  FROM Person 

Here is the result set from running the above query:

Row Number by Record Set FirstName Age
------------------------ ---------- -----------
1 Ted 23
2 John 40
3 George 6
4 Mary 11
5 Sam 17
6 Doris 6
7 Frank 38
8 Larry 5
9 Sue 29
10 Sherry 11
11 Marty 23

The ROW_NUMBER function not only allows you to order the
entire row set, but you can also use the PARTITION clause
to sequentially number groups of rows. Rows will be sequentially
numbered within each unique partition value. The sequential
number will restart at 1 for each new partition value in your
record set. Take a look at the following query:

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person 

When I run this query, this is my result set:

Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Marty 23 M
6 Frank 38 M
7 John 40 M

In this example I partitioned by Gender, and ordered by Age.
Doing this allowed me to sequentially number the female
records in my Person table by age, and then have the sequential
numbering start over again for the male group.

[soup_dog]

Re: Row_Number with OVER/ORDER (V5R4)

Cool! So your saying this Row function is a way of providing a kind of relative record number for your output?

[DeadManWalks]

Re: Row_Number with OVER/ORDER (V5R4)

It apears that this ROW_NUMBER() function is not supported on V5R2. Too bad.

[FaStOnE]

Re: Row_Number with OVER/ORDER (V5R4)

Nope .. it's new with V5R4... sorry DMW...

[DeadManWalks]

Re: Row_Number with OVER/ORDER (V5R4)

I just started a new job, i should have asked them what release they were on first.

[jamief]

Re: Row_Number with OVER/ORDER (V5R4)

Well congrats on the new job...You didnt tell us! I feel hurt.

everyone have a super weekend ...Think bout my white skin glowin in the sun
as Im dragged across the water at high rates of speed on a piece of plastic.

Im taking my son to Spiderman tonight (just thought Id share)

take care congrats again on the new job.
jamie

[FaStOnE]

Re: Row_Number with OVER/ORDER (V5R4)

Boy .. talk about being the Step-child locked in the basement. Thanks DMW... and I thought we were your friends!

(But, congrats on the New Job!)


=====

And btw .. thanks a lot Jamie... now I have another 5-10 Therapy sessions just trying to get that image of your "white skin glowin in the sun" out of my mind.

[soup_dog]

Re: Row_Number with OVER/ORDER (V5R4)

I just started a new job, i should have asked them what release they were on first.

That's exactly what I am going through right now. Thankfully we are getting ready to upgrade our 400 so I won't have to spend too long on this old version.