SQL Language (MOSC)

MOSC Banner

Min and Max value of missing sequenced data

edited Aug 5, 2012 10:35PM in SQL Language (MOSC) 4 commentsAnswered
Hello

Table( Typ, Id )

Typ               Id
-----------------------
T1                 1
T1                 2
T1                 400
T1                 999
T2                 1
T2                 4
T2                 10
T2                  50
T3                  50
T3                  51
T3                  150






From This table data required query should retrieve min and max value of the window of missing numbers where windowsize is let say greater or equal 5
Typ                         MinWinNumber                          MaxWinNumber
----------------------------------------------------------------------------------
T1                            3                                               399
T1                           401                                            998
T2                           5                                                9
T2                           11                                              49
T2                           51                                             999                   /* Attention 999 is highest number allowed by datatype */
T3                           1                                               49
T3                           52                                             149
T3                           151                                           999


Something that woud sound like:

Select Typ, MinWinNumber( Id ), MaxWinNumber( id )
   Tab
 Where id <= 999
Group by Typ
having MaxWinNumber(id ) - MinWinNumber( Id ) >= 5


Is there any way to solve this in decalration way like anaylitical function or subquery/corelated subquery mechanism?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center