Max question

13 Jan 2006 9:18 AM

Lasse Edsvik

Hello

I was wondering if you guys could help me with a simple max select,

Im trying to find the max value where SomeNmbr doesnt follow the increment
step of 1. And no, im not trying to fiddle with the IDENTITY(1,1) thing :)

In this case 4

CREATE TABLE #A (
SomeNmbr int
)

INSERT INTO #A
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 9
UNION
SELECT 10
UNION
SELECT 11

SELECT * FROM #A

DROP TABLE #A

13 Jan 2006 9:38 AM

Jens

Hi lasse,

what about

SELECT MAX(SomeNmbr ) +1
FROM #A

HTH, jens Suessmeyer.

13 Jan 2006 9:41 AM

Lasse Edsvik

Jens,

That would return 12 no matter what the other values are :)

Show quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1137145114.286772.102340@g47g2000cwa.googlegroups.com...
> Hi lasse,
>
> what about
>
> SELECT MAX(SomeNmbr ) +1
> FROM #A
>
> HTH, jens Suessmeyer.
>

13 Jan 2006 9:51 AM

Jens

OK, seems that I didn´t understand your question then, what do you
want to achieve ?

13 Jan 2006 9:59 AM

Lasse Edsvik

Jens,

this seem to work (the number 1 will always exist in table, forgot to
mention that)

SELECT MIN(SomeNmbr)+1 FROM #A A
WHERE NOT EXISTS(SELECT 1 FROM #A B WHERE A.SomeNmbr+1=B.SomeNmbr)

"Jens" <J***@sqlserver2005.de> wrote in message
news:1137145890.297520.232220@z14g2000cwz.googlegroups.com...

OK, seems that I didn´t understand your question then, what do you
want to achieve ?

13 Jan 2006 9:52 AM

Ryan

Hi Lasse, this should give you what you're after.

select t1.SomeNmbr + 1 Lowest,
(select Min(t3.SomeNmbr) - 1
   from #A t3
   where t1.SomeNmbr < t3.SomeNmbr) Upper
from #A t1
left outer join #A t2
    on t1.SomeNmbr + 1 = t2.SomeNmbr
where t2.SomeNmbr is Null

--
HTH. Ryan

Show quote

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:OHlDCJCGGHA.916@TK2MSFTNGP10.phx.gbl...
> Hello
>
> I was wondering if you guys could help me with a simple max select,
>
> Im trying to find the max value where SomeNmbr doesnt follow the increment
> step of 1. And no, im not trying to fiddle with the IDENTITY(1,1) thing :)
>
> In this case 4
>
>
> CREATE TABLE #A (
> SomeNmbr int
> )
>
> INSERT INTO #A
> SELECT 1
> UNION
> SELECT 2
> UNION
> SELECT 3
> UNION
> SELECT 4
> UNION
> SELECT 9
> UNION
> SELECT 10
> UNION
> SELECT 11
>
>
> SELECT * FROM #A
>
> DROP TABLE #A
>
>