SELECT lowest unused figure from range using SQL

Question

I have a table that has a 'location' field that ranges from 1 to 25. I use the following SQL to currently return the lowest 'location' that is currently unused;

select top 1 field from [dbo].[misc_fields] where field NOT IN (select location from ListFields where listid = @listid) order by field

The problem with this is that it is reliant on the misc_fields table which contains a row for each location (1-25). I would like to remove the need to have this lookup table and just define the range within the Stored Procedure itself.

Please enter sample data, expected results, and what you currently have. It is hard to tell what you are talking about. We are not mind readers. — JonH, Sep 19 '12 at 19:31

score 2 · Answer 1 · answered Sep 19 '12 at 19:51

This is the best I can think of right now, a bit more complex than what you have though;

SELECT MIN(pos) AS nextlocation FROM
(
  SELECT location, ROW_NUMBER() OVER (ORDER BY location) AS pos
  FROM ListFields WHERE listid=1
  UNION
  SELECT 0, MAX(location)+1
  FROM ListFields WHERE listid=1
) a
WHERE location<>pos;

Note that this isn't limited to 25, it may very well give out 26, so you'll need to detect your limit yourself elsewhere.

SQLfiddle here to try it out.

Serge · Answer 2 · 2012-09-19T20:23:02.307

0

This solution doesn't work. Serge.

try to use IDENTITY function:

SELECT MIN(artificial_table.id) FROM your_table RIGHT JOIN 
(SELECT TOP 25 IDENTITY(int,1,1) AS id) AS artificial_table 
ON your_table.id = artificial_table.id WHERE your_table.id IS NULL

edited Sep 19 '12 at 20:23

answered Sep 19 '12 at 20:00

Serge

6,088
17
27

The `IDENTITY` function is only useful for `SELECT ... INTO` not to create a standalone adhoc numbers table. – Martin Smith Sep 19 '12 at 20:10
@Martin yes, I read the topic I pointed. Unfortunately I have no MSSQL to try, but I would give it a try. – Serge Sep 19 '12 at 20:15
@Martin, yes, it doesn't work (I found the link to SQLFiddle in the answer below) – Serge Sep 19 '12 at 20:20
Various ways of creating a numbers table on the fly [are discussed here](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232) – Martin Smith Sep 19 '12 at 20:23

score -1 · Accepted Answer · answered Sep 19 '12 at 21:05

-1

Taken from a link provided by Martin Smith;

 WITH Locations (Location) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + Location FROM Locations WHERE Location < 26 
        ) 

SELECT Location INTO #Locations FROM Locations 

SELECT TOP 1 Location from #Locations where Location NOT IN (select location from ListFields where listid = @listid) order by Location

DROP TABLE #Locations

answered Sep 19 '12 at 21:05

ChrisBint

12,773
6
40
62

Gotta love people marking down the accepted (therefore presumably working) answer well over 3 years after it was made, with no comments as to why. – ChrisBint Apr 18 '16 at 15:48

SELECT lowest unused figure from range using SQL

3 Answers3