This might seem an odd question...

SearchlightRG

...but does anyone know if there's a list of sets numbers that have never/not yet been used for Lego sets? Now, bear in mind, I'm not talking about "imaginary" set numbers, as it were. What I mean is, if you have a case where there's a jump from, say, #70819 to #70900, I don't think a list of every number from 70820 to 70899 is necessary. This is more a case of "missing" numbers-like how there's a set #1896 and a set #1898 but not a number 1897. Now, if some intrepid soul has actually put together a list of imaginary and missing Lego sets numbers, based on what's already been released, more power to them. I was just kind of curious.

paul_merton

Your criteria seem rather vague. Why rule out some ranges but not others? Any of them could be used at some point in the future, so none are imaginary as such.

If what you're after is simply a list of unused set numbers that are sandwiched by existing set numbers, then that's easy and the list will be smaller than the number of distinct sets in existence.

Or to answer the opening question, if you want a list of all set numbers that haven't been used yet, then the list will be infinite in length (or nearly 10 million in length if you only include set numbers up to 7 digits long).

Astrobricks

Not sure if this is the OP’s intent, but it would be interesting to see a list of set numbers that were thought to be coming out as real sets but were never released for some reason.

CCC

Dimensions had lots of gaps:

71208
71224,25,26
71243
71249,50
71252
71254
71259,60,61,62,63
71265
71268-84
71288-71339 not in Dimensions, but loads of Bionicle sets here
71341
71345
71347

SearchlightRG

@paul_merton the sandwiched ones are mainly what I’m looking at. I realized as I was creating the post that the concept was an odd one.

@Astrobricks those are certainly applicable as well-I know there have been some where a set was numbered but never released.

560Heliport

70152, 70153, 70154 are Legends of Chima Speedorz that were not released. I know because I really liked Chima. Ow, hey, watch it with the rotten vegetables!

SearchlightRG

Not a lot goin’ on here-must have been a crazy brainwave I guess.

Paperballpark

If you search for set '1' (or similar low number) on brickset, then use the 'next set' button to skip ahead, and note down any gaps. Please come back and let us know what results you get :)

Huw

I'm trying to think of a database query that might generate such a list but haven't come up with anything yet.

If anyone can suggest some TSQL to try I'll run it.

Paperballpark

I couldn't think of anything off the top of my head, but I'm not at a computer this weekend to do any further investigation.

Paperballpark

Actually, thinking about it, there won't be a database query that works, because the queries are designed to return a list of sets as the result. Because what the OP is wanting is a list of numbers that aren't sets, the database - quite reasonably - won't have that information stored, because it only stores information about sets.

brickmatt

In Excel, put a list of all set numbers in the first column and sort ascending. In the first cell of the second column, insert an IF formula that will display text if the difference between the adjacent cell and the next cell (for example, A1 and A2) is greater than 1. Then copy the formula to the rest of the second column. I don't have time to work out the exact formula right now but that should work.

brickmatt

The formula IF(A2-A1>1, "Gap", "") will show where the gaps are. Put that formula in the first cell and copy down to the end of the set numbers by double clicking on the bottom right corner of the cell. Excel will automatically change the cell numbers for the copied formulas. You will have to adjust the cell references if the set numbers start at a different spot. The set numbers must be in ascending order, and the "-1" will have to be removed from the set numbers for this to work, but duplicates won't matter. Here's a couple of screenshots with regular numbers showing how it works.

Fizyx

Paperballpark said:

Actually, thinking about it, there won't be a database query that works, because the queries are designed to return a list of sets as the result. Because what the OP is wanting is a list of numbers that aren't sets, the database - quite reasonably - won't have that information stored, because it only stores information about sets.

Yeah, any automated solution would involve data processing after the query as opposed to a direct query itself.  The best way (and I use 'best' reservedly) I can think to do it would involve basically pulling every record in the database, sorted by set number, and then comparing adjacent pairs of records and recording any that have a difference of more than 1.  Might want to do something around recording if any of those sets match their major set numbers in one of the pairs also.  (EG Set number 600 is shared by 2 sets, #600-1 and #600-2)  Not sure that's necessary though.  But yeah, not a nice solution, and has the potential to not only take up quite a bit of time, but also to potentially hit the DB in a rough way.  Could be done over a couple sessions by splitting the DB queries into smaller chunks, I suppose.

EDIT:  This is essentially @brickmatt's solution, sans the excel :P

ricecake

How about using something like the following to generate the list of numbers from 1 to max(set_id)
https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers
and adding a "where number not in (select set_id from sets...)" clause, but you'd also need to restrict the set_id's to be numeric, and to strip off the "-1" at the end too.

Huw

OK, so here's some crude SQL that's generated the attached list of set numbers less than 10000 that are not allocated, but which are surrounded by ones that are.

It took 16 seconds to run. I could run it to find those < 88004 if it provides what the OP is after, as a one off, although I fail to see that it provides anything interesting or useful!

declare @counter as int
declare @Setnumber as int
declare @allocated as bit

declare @numberBeforeAllocated as bit
declare @numberAfterAllocated as bit


CREATE TABLE #sets (
SetNumber int,
Allocated bit,
Gap int
) ON [PRIMARY]


set @counter=1

while @counter < 10000 begin
insert into #sets(SetNumber,Allocated) values (@counter,0)
SET @counter = @counter + 1
end

Update #sets set Allocated = 1 where setNumber in (Select setnumbernumeric from sets)


DECLARE db_cursor CURSOR FOR 
SELECT SetNumber, Allocated
FROM #sets


OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @Setnumber, @FETCH_STATUS = 0  
BEGIN  

select @numberBeforeAllocated = Allocated from #sets where SetNumber = @SetNumber-1
select @numberAfterAllocated = Allocated from #sets where SetNumber = @SetNumber+1

if @numberBeforeAllocated=1 and @numberAfterAllocated=1 and @allocated=0
update #sets set gap=1 where SetNumber=@Setnumber
 
    FETCH NEXT FROM db_cursor INTO @Setnumber, @allocated
END 

CLOSE db_cursor  
DEALLOCATE db_cursor



select setNumber from #sets where gap=1
drop table #sets

Huw

Sorry, it looks like the forum code has screwed about with the formatting and even hidden some parts of the code, it's interpreting the @xxxx variables as user names...

SumoLego

560Heliport said:

I know because I really liked Chima.

Ha!  We finally found you! 

Please proceed to TRU and buy the last #70145 Ice Mammoth that has been on 5% 'clearance' since 2014.  They need to sell the shelving.

Fauch

I see, the problem is that unused set numbers aren't in the database, since that would take place for nothing, otherwise that should be an easy query.

I wonder if you can look for every possible set number and check if the corresponding value in the database is null. I suspect it may have to check through the whole database until the required set number is found for each set ? or when you find a set, you could put the id in a variable and resume search from it?

my sql is a bit rusty, is there a table named "sets" and another one named "#sets"?

Huw

Yes, sets is the database table containing the data, #sets is a temporary one used within the query.

560Heliport

@SumoLego: I already have two Ice Mammoths! I'd get another for, say, 30% off.

SearchlightRG

...I was wrong...

Goldchains

SumoLego said:

560Heliport said:

I know because I really liked Chima.

Ha!  We finally found you! 

Please proceed to TRU and buy the last #70145 Ice Mammoth that has been on 5% 'clearance' since 2014.  They need to sell the shelving.

My local TRU still has three of those on the shelf.  Lol.

msanders

I would love an Ice Mammoth set. I sold mine a number of years ago and kinda regret it now......

Astrobricks

SearchlightRG said:

...I was wrong...

Apparently, you’re just really good at bumping a thread!