Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle table data query

Posted on 2007-11-27
10
Medium Priority
?
695 Views
Last Modified: 2013-12-18
Hi...i'm a little lost how to fix this....i have a table created by  a inexperienced programmer....the table has been created without  a sequence no or primary key..it has a entry date column but a lot of the data in it is date truncated....

I want to fix the table by adding a sequence no...how do i know the order of data in the table.....if i order it by rownum or rowid, it returns the data inconsistently....data with earlier entry dates appear later than data with later entry dates...is there any wany to retrive the data in the way order it entered the table?

i would'nt mond even moving all the data into another  table with a primary key, but i want to keep the order of the table intact..
0
Comment
Question by:hegde123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 1

Accepted Solution

by:
arunyeshi2000 earned 672 total points
ID: 20362218
Try to print the entry date column value as a timestamp format.
"select date(col,'dd-mm-yyyy hh24:mi:ss") from <table> "

If it has a minutes and seconds with different values then you can order the table with a timestamp convertion.

Something like
insert into <new table> select * from <table> order by to_date(col,'dd-mm-yyyy hh24:mi:ss")
where <new table> structure is a replica of the old table.

Pls let me know if this solves ur problm.
0
 

Author Comment

by:hegde123
ID: 20362262
thanks...but unfortunately i have already tried that...there are records that have the same timestamp and some that are truncated to 00:00 hrs....so i cant say which record precedes which..


is there any other way in oracle to tell which record has entered a table first?
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 664 total points
ID: 20362861
>> i want to keep the order of the table intact..
As long as there are no sequence number columns/no date column with time; you dont have any order of data in the table.

The best you can do here is to make use of the date column in the table(but you have already stated that the date is truncated for some of the records).

You may add a sequence number column to your table and update it with a generated sequence number based on your date columns.

Something like:

SQL> select * from tbl1;

        ID DT
---------- ---------
           28-NOV-07
           29-NOV-07
           27-NOV-07
           26-NOV-07

SQL> update tbl1 t
  2  set id = ( select rn
  3             from (select rowid r_id , row_number() over (order by dt) rn
  4                   from tbl1 )
  5             where r_id = t.rowid)
  6  /

4 rows updated.

SQL> select * from tbl1;

        ID DT
---------- ---------
         3 28-NOV-07
         4 29-NOV-07
         2 27-NOV-07
         1 26-NOV-07
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20363243
It is not possible to find which record entered first and which entered after that and so on
because RDBMS like oracle does not care about the ordering of records in a table.

I think you have to do it manually if you want to do it accurately though it is a painful
activity because you have already told that you are not able to identify which record has come first and so on because there is no time portion in the date field.

can you tell us what other columns in the table do you have, so that we can help you to find some other column if any we can use for this. If you do not have any other way and you do not want to fix it manually, then try sujith80 solution to see if that is fine for you.

Thanks
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 664 total points
ID: 20366754
Folks, how about row-id? Wouldnt that help him sort the table in the way it was entered? I tried it for my table and it seemed to work!
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20366760
SELECT rowid, t.* FROM your_table_name t ORDER BY 1;
0
 

Author Comment

by:hegde123
ID: 20368631
i have tried rowid and it doesnt seem to do the trick either..thanks
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20371977
>> how about rowid

Rowid doesnt ensure any order of data, as DMLs happens on the table, earlier rows may get deleted  and new rows may come in the same rowid.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question