Oracle Illustrated

Feb 21 2010   9:16AM GMT

Few more examples – Analytical Functions

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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);

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: