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.
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 999I need to do a store procedure that returns me the small number not in the tbUSERS.USER_NUM fieldexampleSELECT USER_NUMFROM tbUSERSUSER_NUM---------1101121So, 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 tbUSERSwhere min(USER_NUM+1) not in (SELECT USER_NUM FROM tbUSERS)group by USER_NUMbut it gave the next errors:Server: Msg 147, Level 15, State 1, Line 4An 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 5Incorrect 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 testingDECLARE @Users table (USER_NUM int)-- load sample dataINSERT INTO @Users VALUES(1)INSERT INTO @Users VALUES(10)INSERT INTO @Users VALUES(11)INSERT INTO @Users VALUES(21)-- the actual querySELECT MIN(n.n) AS nFROM Numbers nLEFT OUTER JOIN @Users uON u.USER_NUM = n.nWHERE 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 nFROM tbUSERS nLEFT OUTER JOIN tbUSERS uON u.USER_NUM = n.nWHERE u.USER_NUM IS NULLBut It shows me the next error:Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'n'.So I try with this one and finally it works:select min(n) from(SELECT tb.USER_NUM+1 as nFROM tbUSERS tb) nleft outer join tbUSERS u on n.n=u.user_numWHERE u.USER_NUM IS NULL |
|
|
|
|
|
|
|