DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: duplicate rows

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    duplicate rows

    select * FROM table WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM table GROUP BY timestamp)
    and timestamp between 1038351600 and 1038437940
    order by timestamp DESC;

    I have the sql above giving me duplicate rows for a given day on a specific table. How can I run the same query for all my tables in a given schema, the problem is I have many tables but need to see all the ones with duplicates rather than the sql above which does it table by table, I need to query the data dictionary user_tables etc.

    Any ideas?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use dynamic sql to build the query

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    You have to write a dynamic sql query to generate the necessary sql for all the tables for that schema.

    It should look like:

    ------------------
    set heading off
    spool xxx.sql

    select "select * FROM", table_name , "WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM ", table_name, "GROUP BY timestamp)
    and timestamp between 1038351600 and 1038437940
    order by timestamp DESC; " from user_tables;

    spool off
    --------------------------

    You can then run the xxx.sql to get the output you want(each row for each table for the schema).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width