CODEWITHSUNDEEP
sqlsqlitemax
user3808023
user3808023

Reputation: 1

select max value in a group of consecutive values

How do you do to retrieve only the max value of a group with only consecutive values?

I have a telephone database with only unique values and I want to get only the highest number of each telephone number group TelNr and I am struggling.

id     |      TeNr       |      Position
1      |      100        |     SLMO2.1.3
2      |      101        |     SLMO2.3.4
3      |      103        |     SLMO2.4.1
4      |      104        |     SLMO2.3.2
5      |      200        |     SLMO2.5.1
6      |      201        |     SLMO2.5.2
7      |      204        |     SLMO2.5.5
8      |      300        |     SLMO2.3.5
9      |      301        |     SLMO2.6.2
10     |      401        |     SLMO2.4.8

Result should be:

TelNr
101
104
201
204
301
401

I have tried almost every tip I could find so far and whether I get all TelNr or no number at all which is useless in my case.

Any brilliant idea to run this with SQLITE?

Upvotes: 0

Views: 272

Answers (1)

dnoeth
dnoeth

Reputation: 60482

So you're searching for gaps and want to get the first value of those gaps.

This is probably the best way to get them, try to check for a row with the current TeNr plus 1 and if there's none you found it:

select t1.TeNr, t1.TeNr + 1 as unused_TeNr
from tab as t1
left join Tab as t2
on t2.TeNr = t1.TeNr + 1
where t2.TeNr is null

Edit:

To get the range of missing values you need to use some old-style SQL as SQLite doesn't seem to support ROW_NUMBER, etc.

select
   TeNr + 1 as RangeStart, 
   nextTeNr - 1 as RangeEnd, 
   nextTeNr - TeNr - 1 as cnt
from
 (
   select TeNr, 
     ( select min(TeNr) from tab as t2
       where t2.TeNr > t1.TeNr ) as nextTeNr
   from tab as t1
 ) as dt
where nextTeNr > TeNr + 1

It's probably not very efficient, but might be ok if the number of rows is small and/or there's a index on TeNr.

Getting each value in the gap as a row in your result set is very hard, if your version of SQLite supports recursive queries:

with recursive cte (TeNr, missing, maxTeNr) as
 (
   select
      min(TeNr) as TeNr,   -- start of range of existing numbers
      0 as missing,        -- 0 = TeNr exists, 1 = TeNr is missing
      max(TeNr) as maxTeNr -- end of range of existing numbers
   from tab 

   union all

   select
      cte.TeNr + 1,        -- next TeNr, if it doesn't exists tab.TeNr will be NULL
      case when tab.TeNr is not null then 0 else 1 end, 
      maxTeNr
   from cte left join tab
   on tab.TeNr = cte.TeNr + 1
   where cte.TeNr + 1 < maxTeNr
  )  
select TeNr 
from cte
where missing = 1

Depending on your data this might return a huge amount of rows. You might also use the result of the previous RangeStart/RangeEnd query as input to this recursion.

Upvotes: 1

Related Questions