About Me

Working as Oracle professional over 7 years now. Specialized in - advanced PL/SQL, advanced SQL queries, Performance Tuning & Designing. Have completed Oracle Advanced PLSQL certification (OCP)

Wednesday, February 3, 2010

Few more examples - Analytical Functions

FINDING MISSING SEQUENCE / NUMBER from a table

SELECT /*+ ALL_ROWS */
pre_gap+1 start_gap, max_seq_gap
FROM
(SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap ,
(SELECT /*+ ALL_ROWS */ MAX(seq_column) FROM table_name) max_seq_gap
FROM table_name)
WHERE pre_gap != 0
AND post_gap - pre_gap > 1

Delete duplicates

DELETE FROM table_name tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY ORDER BY ) duplicate FROM table_name ) qry
WHERE qry.duplicate > 1);

No comments:

Post a Comment

Followers