How to find out next available number


Problem :

In my MySQL table I have field called sequence where I have values like 1 , 2, 3, 5, 6, 7, 8, 10 some of the sequence number are skiped due to deleted records. How do I find out next available number from given number. let's say if I need next number from 3 , how do I get number 5 as my next number in sequence not the 4.



Solution :

To find out the next ID after 3 that appears in your table, you should do

SELECT id FROM thetable WHERE id>3 ORDER BY id ASC LIMIT 1

This just considers IDs that are greater than 3, in ascending order, and then takes the first one on that list. If it returns you one result, then that's the next one used in the table; if it doesn't return a result at all, then the ID you gave it was already the highest one in the table (or, strictly speaking, at least as high as the highest one in the table).


Mysql Tutorials

Mysql Howto..

How to find points, sort them based on closeness, and get 1-20, 21-40, etc. Efficiently using Myisam and mysql and spatial index

How to add rank based on points in mysql

How to de-duplicate records in MySQL?

How to wrap mysql in a python script

How to check the default port number for mysql connection? Connection not working! jdbc connection

How to save URL's to a MYSQL database from an array

How to check which cache features are turned on? [PHP/MYSQL]

How to Trigger total number of days in mySQL

how to sent the ANY operand mysql

How to use async Mysql query with PHP PDO

How to create a MYSQL backup with custom names and information

How to extract data from Mysql column stored in serialized JSON column?

How to filter record set with unknown column in MySQL [closed]

how to mysql_fetch_array on joined tables, but columns have same name [duplicate]

How to increment rank in mysql only when the 2 columns (duration and score) are different?

How to fill Charts.js with mysql data?

How to connect an Android application to a MySQL database?

How to insert a json object into a mysql field and read it back?

Using wamp server how can I have mysql directory on another location?

How to simplify the result of UNION ALL

How to remove tabs at start and end of varchar field in MySQL?

How do I setup MySQL queries to receive Backbone pagination requests?

How Do I Calculate the Number of the Following Week?

How to get a total count when also grouping in sql?

how to compare two mysql table in php

How to sort ENUM column in MySQL database?

How to create a column in MYSQL which automatically SUMs the values from other columns within the same row

How do I write a javascript variable to an sql database?

How to make innodb as default engine

How to Have Search Engines Index Database-Driven Content?