Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

oracle table data query

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
hegde123
Asked:
hegde123
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
arunyeshi2000Commented:
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
 
hegde123Author Commented:
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
 
sujith80Commented:
>> 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Jinesh KamdarCommented:
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
 
Jinesh KamdarCommented:
SELECT rowid, t.* FROM your_table_name t ORDER BY 1;
0
 
hegde123Author Commented:
i have tried rowid and it doesnt seem to do the trick either..thanks
0
 
sujith80Commented:
>> 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now