How to get the max sequential value?

Question

Having a table with a column that represents a enumeration:

CASE 1

| CustomID | Values |
|     '001'|     ...|
|     '002'|     ...|
|     '005'|     ...|
|     '006'|     ...|

I need to get the max value in sequential order, in this case: '002'

| CustomID | Values |
|     '028'|     ...|
|     '029'|     ...|
|     '031'|     ...|
|     '032'|     ...|

Result expected: '029'.

CASE 2 But if the data is like this:

| CustomID | Values |
|     '001'|     ...|
|     '002'|     ...|
|     '003'|     ...|
|     '004'|     ...|

I need to get '004'.

Really not a good idea to be storing numbers as strings. For one thing, it's going to make this a lot slower... — RBarryYoung, Nov 30 '15 at 16:36
@RBarryYoung I cannot edit the column type. The table already exists. — Erick Asto Oblitas, Nov 30 '15 at 16:44
Possible duplicate of [How to find a "gap" in running counter with SQL?](http://stackoverflow.com/questions/1312101/how-to-find-a-gap-in-running-counter-with-sql) — Tab Alleman, Nov 30 '15 at 16:46

Gordon Linoff · Answer 1 · 2015-11-30T18:57:18.087

1

You need the first time that a gap is not one. Assuming the values are actually numbers, the easiest way is to use lead():

select min(t.customId)
from (select t.*,
             lead(customId) over (order by CustomId) as next_customId
      from t
     ) t
where next_CustomId is null or next_CustomId <> CustomId + 1;

EDIT:

In SQL Server 2008, you can do the same thing with outer apply:

select min(t.customId)
from (select t.*,
             next.customId as next_customId
      from t outer apply
           (select top 1 t2.*
            from t t2
            where t2.CustomId > t.CustomId
            order by t2.CustomId
           ) next
     ) t
where next_CustomId is null or next_CustomId <> CustomId + 1;

edited Nov 30 '15 at 18:57

answered Nov 30 '15 at 16:41

Gordon Linoff

1,242,037
58
646
786

Assuming he's got a later version of SQL Server that has `LEAD()` of course (can't remember, was it SQL Server 2012?) – RBarryYoung Nov 30 '15 at 16:45
1

@Gordon Linoff Lead does not exists in SQL Server 2008 – Erick Asto Oblitas Nov 30 '15 at 16:48
@ErickAstoOblitas . . . The question was not tagged SQL Server 2008 when I answered it. – Gordon Linoff Nov 30 '15 at 18:56

score 0 · Answer 2 · answered Nov 30 '15 at 16:40

0

Something like this should work:

;WITH cte As
(
    SELECT CAST(CustomID As INT) As cid FROM yourTable
)
SELECT MIN(cid) As MaxSeq
FROM   cte As c1
WHERE  NOT EXISTS(Select * From cte As c2 Where c2.cid = c1.cid+1)
;

answered Nov 30 '15 at 16:40

RBarryYoung

55,398
14
96
137

Lukasz Szozda · Answer 3 · 2015-11-30T17:08:50.830

-1

There is no need for windowed function/cte/subqueries. You can use simple LEFT JOIN:

SELECT MIN(t.CustomID)
FROM #tab t
LEFT JOIN #tab t1
  ON t.CustomID = t1.CustomID - 1
WHERE t1.CustomID IS NULL;

LiveDemo

You need to find first record that does not have consecutive record. Using self outer join and where you will get records that don't have consecutive records and MIN to get first of them.

edited Nov 30 '15 at 17:08

answered Nov 30 '15 at 16:52

Lukasz Szozda

162,964
23
234
275

**For funny downvoter, leave a comment why this code is bad** – Lukasz Szozda Nov 30 '15 at 17:01
not the down voter but if you try your solution with `INSERT INTO #tab VALUES ('001'),('002'),('003'),('005'),('006')` , it should return `004` but it doesnt :) – M.Ali Nov 30 '15 at 17:08
@M.Ali It should return `003`. It is the first gap - 1. Max sequential value starting from beginning. Check OP examples. Am I right? – Lukasz Szozda Nov 30 '15 at 17:09
read my comment again, if you insert the value `003` in the table it will still return `003` I think. – M.Ali Nov 30 '15 at 17:13
@M.Ali I run your [example](https://data.stackexchange.com/stackoverflow/query/401179) and it return `003` why is it wrong? OP doesn't want first gap which is 004. Check first OP example `('001', '002', '005', '006')` => `'002'` not `'003'` – Lukasz Szozda Nov 30 '15 at 17:13

How to get the max sequential value?

3 Answers3