1

Let's say I have a table T, that has one attribute, A, an integer.

How can I make the SQL query return MAX(T) + 1 if T contains a 5 and MAX(T) + 2 if it doesn't?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rose Perrone
  • 61,572
  • 58
  • 208
  • 243
  • What's the real task behind this strange requirement? PS: what actual DBMS do you use? – zerkms Jul 12 '12 at 05:25
  • I'm using SQLite. I'm trying to return the smallest positive integer that's not present in a sequence of positive integers. e.g. return 3 if the sequence is 2, 6, 7. Return 5 if the sequence is 1, 2, 3, 4. Return 2 if the sequence is 3, 4. – Rose Perrone Jul 12 '12 at 05:28
  • "return 3 if the sequence is 2, 6, 7." --- why not 1? "Return 2 if the sequence is 3, 4" --- why not 1? – zerkms Jul 12 '12 at 05:31
  • 2
    I think you need this: http://stackoverflow.com/questions/684106/find-the-smallest-unused-number-in-ms-sql – Luxspes Jul 12 '12 at 05:32

3 Answers3

2
SELECT MAX(a) + CASE WHEN (5 IN (SELECT a FROM t)) THEN 1 ELSE 2 END AS max_plus_something FROM t
LisMorski
  • 399
  • 2
  • 7
1
SELECT MAX(A) + MIN(CASE A WHEN 5 THEN 1 ELSE 2 END) AS NewA FROM T
Andriy M
  • 76,112
  • 17
  • 94
  • 154
0

I do not have the chance to test many cases but i think this can do what you want.

declare @MinPosNI int;
set @MinPosNI = 1;
select @MinPosNI = case when (A - @MinPosNI) > 0 then @MinPosNI else A + 1 end from T     where A > 0 order by A 
select @MinPosNI
Andriy M
  • 76,112
  • 17
  • 94
  • 154
almi_n
  • 51
  • 3