Databases
StackOverflow2013
Postsdbo
POMySQL: find out missing order IDs
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: find out missing order IDs
    text
    copied!<p>i Know this question asked several times in StackOverFlow. i have tried few of them but i am out of luck. </p> <p>i have a MySQL table where there is a field(<code>orders_id</code>) which is might appear randomly in the table (Not sequentially) and i need to find out which ids are missing from the table.</p> <pre> orders_id product_name qty 1007 Wireless Mouse 1 1000 Laptop 1 1004 Wireless Mouse 3 1020 PC 3 1003 Wireless Mouse 4 1025 IPAD 4 1026 iphone 1 </pre> <p><strong>Expected Answer:</strong></p> <p>Assume <code>orders_id</code> start from <strong>1000</strong>.</p> <pre> orders_id 1000 1001 1002 1005 1006 1008 ...... </pre> <p><strong>i have already created above table at "SqlFiddle" , you guys can use it.</strong></p> <p><strong><a href="http://sqlfiddle.com/#!2/eeab0" rel="nofollow noreferrer">See table: SQLfiddle</a></strong></p> <p>**the SQL i have tried: **</p> <pre><code>declare @id int declare @maxid int set @id = 1 select @maxid = max(`orders_id`) from orders create temporary table IDSeq ( id int ) while @id &lt; @maxid begin insert into IDSeq values(@id) set @id = @id + 1 end select s.id from idseq s left join orders t on s.id = t.`orders_id` where t.`orders_id` is null drop table IDSeq </code></pre> <p>I have taken above SQL from the following Answer:</p> <p><a href="https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table">SQL: find missing IDs in a table</a></p> <p><strong>i have also tried the ANSI SQL:</strong></p> <pre><code>SELECT a.orders_id+1 AS start, MIN(b.orders_id) - 1 AS end FROM orders AS a, orders AS b WHERE a.orders_id &lt; b.orders_id GROUP BY a.orders_id HAVING start &lt; MIN(b.orders_id) </code></pre> <p>Anyone has any idea?? how can i find out the missing orders id.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload