SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding missing numbers in a sequence prior to first existing number

    I have been using this (well circulated) method to ascertain the first missing number in a sequence from a selection of IDs from a specific column (‘song_name’) in a table (‘song’) so as to reassign deleted IDs to future entries (these field names are just examples):-

    Code:
    SELECT l.id+1 AS start
    FROM (SELECT id FROM song WHERE song_name LIKE ‘imagine’) AS l
    LEFT OUTER JOIN (SELECT id FROM song WHERE song_name LIKE ‘imagine’) AS r ON l.id+1 = r.id
    WHERE r.id IS NULL
    ORDER BY start ASC LIMIT 1;
    Code:
    ID	SONG
    1	Imagine
    2	Imagine
    3	Imagine
    6	Imagine
    7	Imagine
    8	Imagine
    1	Eleanor Rigby
    2	Eleanor Rigby
    3	Eleanor Rigby
    If the sequence of IDs is: 1,2,3,6,7,8, then it will correctly return 4 as the result.

    However, if the sequence is: 2,3,6,7,8, then it will still return 4 as the result, even though 1 is the first missing number (it only finds the first missing number from after the sequence starts, i.e. if the sequence is 50,51,52,54,55, then the result would be 53, disregarding the missing IDs of 1 to 49).

    My solution to this would be to first run a separate command to check specifically if 1 is found in the selected IDs, and if not then assign 1 as the ID. But if 1 is found, then run the above command, as any gap in the sequence following 1 would definitely be the first.

    But I am wondering if there is a more elegant solution that I have not thought of? Perhaps using the WHERE EXISTS / WHERE NOT EXISTS command?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by andygout View Post
    But I am wondering if there is a more elegant solution that I have not thought of?
    yes, there is, and it is to completely disregard the gaps in the numbers, and stop trying to renumber them after deletions
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, having thought over it, re-assigning deleted IDs would be a bad idea from the point of view that people saving URLs (which will include an ID) would then get a page featuring a different song rather than some sort of 'entry deleted' message (the latter obviously being the preferred outcome).

    This is the code I will be using to identify the next available ID (I am not using AUTO_INCREMENT for this column as I want an individual ID count for each different song) (credit to Lukáš Lalinský at Stack Overflow):-

    Code:
    SELECT 1 + coalesce ((SELECT MAX(id) FROM song
    WHERE song_name LIKE ‘$song_name’), 0) AS id;
    However, it seems people might still need to identify the beginning of all gaps in a sequence (see Matthew Frederick's comment), for which the below code does the trick:-

    Code:
    SELECT t1.id+1 AS id FROM song t1
    WHERE NOT EXISTS (SELECT 1 FROM song t2 WHERE t2.id = t1.id+1
    AND song_name LIKE ‘$song_name’)
    UNION
    SELECT 1 AS id FROM song
    WHERE NOT EXISTS (SELECT 1 FROM song WHERE id = 1
    AND song_name LIKE ‘$song_name’)
    ORDER BY id ASC LIMIT 1;
    This is taken from Darrell Miller's solution posted on Stack Overflow. I had a problem with the TOP function (apparently not all database systems support the TOP clause), but using ORDER BY id ASC LIMIT 1 at the end of the command will do the same thing.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •