OraFAQ Forum: SQL & PL/SQL » increment rowcount with each value retreived for a main column

Home » SQL & PL/SQL » SQL & PL/SQL » increment rowcount with each value retreived for a main column

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

increment rowcount with each value retreived for a main column [message #20570]

Wed, 05 June 2002 08:17

meenu sharma
Messages: 5
Registered: April 2002

Junior Member

I have a problem where by i want to see the rowcount
incremented by 1 if a particular column has multiple values in a table i.e. 1 to many relation.
eg. if table A has following definition -
order_number invoice_number
1000 10
1000 11
1000 12
2000 20
2000 21

then my query should give me following result
order_number invoice_number line_number

1000 10 1
1000 11 2
1000 12 3
2000 20 1
2000 21 2

Any help in this regard is appreciated.
Thanks,
Meenu

Report message to a moderator

Re: increment rowcount with each value retreived for a main column [message #20572 is a reply to message #20570]

Wed, 05 June 2002 09:53

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

sql>select order_number, invoice_number, 
  2         row_number() over (partition by order_number order by invoice_number) row_number
  3    from t
  4   order by order_number, invoice_number;
 
ORDER_NUMBER INVOICE_NUMBER ROW_NUMBER
------------ -------------- ----------
        1000             10          1
        1000             11          2
        1000             12          3
        2000             20          1
        2000             21          2

Report message to a moderator

Re: increment rowcount with each value retreived for a main column [message #20598 is a reply to message #20570]

Thu, 06 June 2002 04:03

shyampaliyath
Messages: 22
Registered: May 2002

Junior Member

hai meenu sharma,

why not try this??

select a.order_number,a.invoice_number,count(*)
FROM tableA a
WHERE rowid > ( SELECT min(rowid)
FROM tableA b
WHERE b.order_number = a.order_number
and b.invoice_number= a.invoice_number)

with regards
Shyam
group by a.order_number,a.invoice_number;

Report message to a moderator

Previous Topic:	I need a SQL-Statement or function
Next Topic:	Ref Cursor in Select statement-URGENT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 15:36:17 CDT 2024