OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Query to rank

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

Query to rank [message #248312]

Thu, 28 June 2007 10:51

mvnrajan
Messages: 5
Registered: June 2007

Junior Member

Hi guys,

Am strugling to write a query. I need output as follows.

I have companyid with duplicates in a table. That table contains
only companyid. For example, only 2 companyid exist in table with duplicates.
Column SEQ has to display as 1 for first companyid and 2 for second companyid.DRANK column has to display a rank corresponding to companyid.

I need a output as follows

Any guys please..........

companyid ID SEQ RANK
----------------------------------------
CH46911 1 1
CH46911 1 2
CH46911 1 3
CH46911 1 4
GB24158 2 1
GB24158 2 2
GB24158 2 3
GB24158 2 4

Attachment: aaa.txt
(Size: 0.26KB, Downloaded 542 times)

Report message to a moderator

Re: Query to rank [message #248314 is a reply to message #248312]

Thu, 28 June 2007 11:04

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

select companyid,
       decode(companyid,'CH46911',1,2) id,
       row_number() over(partition by companuid order by rowid) seq
from mytable
/

Regards
Michel

Report message to a moderator

Re: Query to rank [message #248497 is a reply to message #248314]

Fri, 29 June 2007 03:50

mvnrajan
Messages: 5
Registered: June 2007

Junior Member

Your query helped me to solve my problem Michel....

Thanks a lot.......

Cheers........

Regards
Nataraj

Report message to a moderator

Previous Topic:	How to spool output of PL/SQL procedure/function to file?
Next Topic:	How to detect the special characters in string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 07:14:25 CST 2024