Home » SQL & PL/SQL » SQL & PL/SQL » Need Query to simulate ROW_NUMBER
Need Query to simulate ROW_NUMBER [message #236508] Thu, 10 May 2007 02:30 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
We are facing some strange issue here i.e we were using row_number in oracle 9i. It was working fine. however when we migrate the same code to oracle 10g, the analytical function row_number behaving strange i.e it is not assigning unique sequence number in the particular window. We also reported this to oracle support team. They too accepted as bug. So in the mean time we want to simulate the functionality of ROW_NUMBER in SQL 10g. If any one of you have it, please share it across.


Thanks,
Thangam
Re: Need Query to simulate ROW_NUMBER [message #236515 is a reply to message #236508] Thu, 10 May 2007 02:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Would it be strange if I asked for:
1. create table statement
2. insert statements with sample data
3. your SQL statement
4. the output you got from that sample data
5. the expected output based on that sample data

You have this problem in one window only? Don't hesitate to keep us informed.

MHE

Edit: does DENSE_RANK() have the same flaw?

[Updated on: Thu, 10 May 2007 02:42]

Report message to a moderator

Re: Need Query to simulate ROW_NUMBER [message #236599 is a reply to message #236515] Thu, 10 May 2007 06:05 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Sorry for this incomplete information(Anyway I cannot provide all the information). However this is the real time table with huge volume of data(nearly 80 to 100 million per partition) and 300+ columns. If we try with sample table with mock data, row_number is yielding desired output, but when it comes to huge data it is giving strange output. Please find the screen shot attached.
./fa/2435/0/
Hence till we get the batch from Oracle support, i want to simulate the ROW_NUMBER functionaly in the SQL query.

Thanks,
Thangam
Re: Need Query to simulate ROW_NUMBER [message #236683 is a reply to message #236599] Thu, 10 May 2007 11:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you send this to Oracle Support too? I bet they were impressed.. This picture doesn't tell me nothing.
If you want us to help, you have to provide what Maaher asked for.

Previous Topic: Replication, Streams, Merge or MVs?
Next Topic: Select from different database
Goto Forum:
  


Current Time: Sat Dec 03 07:45:29 CST 2016

Total time taken to generate the page: 0.08889 seconds