OraFAQ Forum: SQL & PL/SQL » Running Row Count

Home » SQL & PL/SQL » SQL & PL/SQL » Running Row Count

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Running Row Count [message #9785]

Thu, 04 December 2003 13:23

Khadija Smith
Messages: 5
Registered: December 2003

Junior Member

I am trying to get a running sum value for each row that is returned from a select statement
EXAMPLE:
select values from any_table

1, 2, 3, 4 indicate a running count of the rows that are returned

1 values
2 values
3 values
4 values

Report message to a moderator

Re: Running Row Count [message #9786 is a reply to message #9785]

Thu, 04 December 2003 14:04

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

So, is it a "running count of the rows" or a "running sum value"? Two different things.

Report message to a moderator

Re: Running Row Count [message #9787 is a reply to message #9786]

Thu, 04 December 2003 14:16

Khadija Smith
Messages: 5
Registered: December 2003

Junior Member

A sequential running count of the rows...I have tried using rownum but when I use an order by in my select statement this mixes up the numbering.

Report message to a moderator

Re: Running Row Count [message #9788 is a reply to message #9787]

Thu, 04 December 2003 14:23

Khadija Smith
Messages: 5
Registered: December 2003

Junior Member

I have tried using row_number() OVER...

select x, row_number() OVER (order by x) as rownumber from tablename

But I get this error
ORA-00923: FROM keyword not found where expected

Report message to a moderator

Re: Running Row Count [message #9789 is a reply to message #9788]

Thu, 04 December 2003 14:46

Khadija Smith
Messages: 5
Registered: December 2003

Junior Member

I am using ORACLE 8.1.5 is there another way to do this without using the:
row_number() OVER (order by fieldname)
function

Report message to a moderator

Re: Running Row Count [message #9790 is a reply to message #9789]

Thu, 04 December 2003 16:49

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

The key is to apply rownum after the ordering in the inline view:

select t.*, rownum
  from (select *
          from t
         order by c1) t;

Report message to a moderator

Re: Running Row Count - THANKS!!! [message #9815 is a reply to message #9790]

Fri, 05 December 2003 10:54

Khadija Smith
Messages: 5
Registered: December 2003

Junior Member

Thanks a bunch for this info...

Report message to a moderator

Previous Topic:	Select from spBuildCursor()
Next Topic:	Optimizing Delete cascade with hints

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 09:14:35 CDT 2024