Find wholes in letter/number sequences using self-joins

Language: English
Programming Language: SQL
Published by: vic4 [not registered]
Last Update: 5/15/2006
Views: 1182

Description

If you have a data dictionary at your work and you want to add a new list of values requiring [for example] 7 elements, then you can either start your elements at the very end of the dictionary [select max(id)+1] or you can use these SQL examples to find a contiguous gap somewhere in the middle.

Code

Display line numbers

1 create table test (letter char(1)); 2 insert into test values ('A'); 3 insert into test values ('B'); 4 insert into test values ('C'); 5 insert into test values ('D'); 6 insert into test values ('F'); 7 insert into test values ('H'); 8 insert into test values ('I'); 9 insert into test values ('Q'); 10 insert into test values ('Z'); 11 12 13 --FIND THE FIRST LETTER IN A SEQUENCE OF UNUSED LETTERS SUCH THAT 14 --IT IS THE SHORTEST AVAILABLE SEQUENCE OF LETTERS THAT CAN FIT *7* MEMEBERS. 15 --IF THERE IS MORE THAN ONE SUCH SEQUENCE, ORDER THE SEQUENCES FROM SMALLEST TO LARGEST 16 select chr(ascii(A.letter) + 1) X, 17 ascii(min(B.letter)) - ascii(A.letter) - 1 SEQ_SIZE 18 from test A, test B 19 where B.letter > A.letter 20 group by A.letter 21 having ascii(min(B.letter)) - ascii(A.letter) > 7 22 order by ascii(min(B.letter)) - ascii(A.letter); 23 24 25 26 27 28 29 30 --FIND THE FIRST LETTER IN A SEQUENCE OF UNUSED LETTERS SUCH THAT 31 --IT IS THE SHORTEST AVAILABLE SEQUENCE OF LETTERS THAT CAN FIT [7] MEMEBERS. 32 --IF THERE IS MORE THAN ONE SUCH SEQUENCE, THE LETTER THAT IS LOWEST IS RETURNED 33 34 35 --METHOD #1 36 --While this clearly returns the result, the subquery might make this method the least efficient 37 select min(chr(ascii(min(A.letter)) + 1)) X 38 from test A, test B 39 where B.letter > A.letter 40 group by A.letter 41 having ascii(min(B.letter)) - ascii(A.letter) = 42 ( 43 select min(ascii(min(B.letter)) - ascii(A.letter)) 44 from test A, test B 45 where B.letter > A.letter 46 group by A.letter 47 having ascii(min(B.letter)) - ascii(A.letter) > 7 48 ); 49 50 51 --METHOD #2 52 --The format is 'XXA' where A is the first free letter and XX is a two digit 53 --number left padded with 0's indicating the actual size of the identified sequence. 54 --This approach leaves it up to the user to pull out his values, but it 55 --looks like it may be the most efficient 56 select min(lpad(ascii(min(B.letter)) - ascii(A.letter) - 1, 2, '0') || chr(ascii(A.letter) + 1)) X 57 from test A, test B 58 where B.letter > A.letter 59 group by A.letter 60 having ascii(min(B.letter)) - ascii(A.letter) > 7; 61 62 63 --METHOD #3 64 --This is method #2 with the values parsed out 65 select to_number 66 ( 67 substr 68 ( 69 min(lpad(ascii(min(B.letter)) - ascii(A.letter) - 1, 2, '0') || chr(ascii(A.letter) + 1)), 70 1, 71 2 72 ) 73 ) SEQ_SIZE, 74 substr 75 ( 76 min(lpad(ascii(min(B.letter)) - ascii(A.letter) - 1, 2, '0') || chr(ascii(A.letter) + 1)), 77 3, 78 1 79 ) X 80 from test A, test B 81 where B.letter > A.letter 82 group by A.letter 83 having ascii(min(B.letter)) - ascii(A.letter) > 7;

One comment

Use full.But its only show a,b,c,d,f,h,i,q,z.

__________________________________________________
visit
http://www.ambitmall.com

Saturday, September 06, 2008 4:48:48 AM from Varun

Add a comment

Name *

Email (won't be displayed) *

Website

Comment *

Sicherheitscode Security Code *