Finding Skipped Numbers in a Mysql Table

[X51]

I have a table that contains invoice numbers and I want to find any numbers that are not used. So I borrowed and modified the following query to do just that and it works fine.

 

SELECT start, stop FROM (SELECT m.invoice_number + 1 AS start, (SELECT MIN(invoice_number) - 1 FROM payments AS x WHERE x.invoice_number > m.invoice_number) AS stop FROM payments AS m LEFT OUTER JOIN payments AS r ON m.invoice_number = r.invoice_number - 1 WHERE r.invoice_number is null) AS x WHERE stop is not null

 

The problem is that I have a column named 'active' in the table that is either a 1 (actively used number) or a 0 (inactive) I want this query to consider an inactive record as a missing number and reuse that invoice number, but am unable to figure out exactly how to do that. Every time I introduce 'WHERE active = 1' into the query it either says unknown column or that active is ambiguous.

 

I was hoping someone could point me in the right direction to make this happen. My version is 5.1

[ignace]

Without having some demo data it's hard to write this query off the top of my head, but could something like this work?

 

(
-- invoice_numbers marked as inactive should be considered for re-use
SELECT invoice_number AS `Missing From`, invoice_number AS `To`
FROM payments
WHERE active=0
)
UNION (
-- find unused invoice_numbers
-- Source: http://www.artfulsoftware.com/infotree/queries.php#71
SELECT a.invoice_number + 1 AS `Missing From`, MIN(b.invoice_number) - 1 AS `To`
FROM payments a, payments b
WHERE a.invoice_number < b.invoice_number
GROUP BY a.invoice_number
HAVING `Missing From` < MIN(b.invoice_number)
)

[X51]

Thanks for the reply.

 

As I have been sitting here thinking about this I realized that my thinking is flawed.

 

First,  I have the invoice_number column set to 'unique' so I wont have duplicates (easily fixed by removing it).

Second, since that record will always be inactive that number will always come up as the next invoice number unless there is also a check to see if it has been previously re-used.

 

I normally don't delete info from a table, but I'm starting to think that my best bet would be to delete the record rather than make it inactive then there would be no problem in that area and my original query would work fine.

 

The reason why I don't like to delete is that the table always creates overhead and needs to be optimized and I don't know what problems are created by that.

[ignace]

I normally don't delete info from a table

 

And you shouldn't. At the very least export these rows to an archive table.

[DavidAM]

I normally don't delete info from a table

 

And you shouldn't. At the very least export these rows to an archive table.

 

If the application calls for a DELETE, then you delete. You just need to design your application properly so you don't delete valuable data.

 

From the standpoint of table fragmentation, an UPDATE is just as bad as a DELETE. Unless ALL of your columns are fixed length, and NONE of your columns allow NULL; then the row length varies, and it is not possible to do an update-in-place. The row is typically "moved" to another location in the "table space" and the table fragments. Also, if any of your indexed columns are being updated, the indexes are (likely) getting fragmented as well. A database, like a car, requires periodic maintenance.

[X51]

That's pretty much what I thought, but wasn't sure about. I will stick to my non-delete policy

 

I am going to consider this closed and work on another solution to this problem that doesn't include deleted rows. Thanks again for the valuable information!