SP that return the minor number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP that return the minor number

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2005-05-09 : 18:38:46

I have a table tbUSERS that have a field USER_NUM smallint, this field store a number between 1 and 999

I need to do a store procedure that returns me the small number not in the tbUSERS.USER_NUM field

example
SELECT USER_NUM
FROM tbUSERS

USER_NUM
---------
1
10
11
21

So, if I make the store procedure it must returns me the number 2.

I really don't have a idea how to do this, thanks for your time, I hope you understand my question, I have log time with out using English ( and SQL too).

---New Idea-----
I was thinking on this:

SELECT min(USER_NUM+1)
FROM tbUSERS
where min(USER_NUM+1) not in (SELECT USER_NUM FROM tbUSERS)
group by USER_NUM

but it gave the next errors:
Server: Msg 147, Level 15, State 1, Line 4
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'group'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-09 : 18:52:45

This is easy if you have a Numbers table. This table should have one column, named n, which is an integer. Load the table with numbers 1-8000. Then:



-- for testing
DECLARE @Users table (USER_NUM int)

-- load sample data
INSERT INTO @Users VALUES(1)
INSERT INTO @Users VALUES(10)
INSERT INTO @Users VALUES(11)
INSERT INTO @Users VALUES(21)

-- the actual query
SELECT MIN(n.n) AS n
FROM Numbers n
LEFT OUTER JOIN @Users u
ON u.USER_NUM = n.n
WHERE u.USER_NUM IS NULL

Tara

shifis
Posting Yak Master

157 Posts

Posted - 2005-05-09 : 19:22:00

Thanks Tara you just gave me an idea. First I do the next code, to avoid the uses of another table.

SELECT min(n.USER_NUM+1) as n
FROM tbUSERS n
LEFT OUTER JOIN tbUSERS u
ON u.USER_NUM = n.n
WHERE u.USER_NUM IS NULL

But It shows me the next error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'n'.

So I try with this one and finally it works:

select min(n) from
(SELECT tb.USER_NUM+1 as n
FROM tbUSERS tb) n
left outer join tbUSERS u on n.n=u.user_num
WHERE u.USER_NUM IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources