Connor Bishop Connor Bishop - 4 months ago 8
SQL Question

How to perform this query

So I have a table in my data base (Job Cards), which has a column called 'JobCardNum'. This is the primary key for the table, however there are gaps because a particular job card may have been deleted.

For example the may exist Job Cards with number 2000 and 2002 but there is no 2001.

What I need to is take a JobCard Number as an input and find what the next Job Card Number would be if the column was in ascending order.

So if '$num' is my sanitised input, I need something like

"SELECT * FROM JobCards WHERE JobCardNum = $num ORDER BY 'JobCardNum' [asc]"


But then get the 'next' JobCardNum.

I know I could return an array of all JobCards then extract the one I need but there could be thousands of entries so this seems inefficient.

Any Ideas

Answer
SELECT TOP 1 JobCardNum FROM JobCards WHERE JobCardNum > $num ORDER BY JobCardNum asc

mysql:

SELECT JobCardNum from someTable WHERE JobCardNum > $num ORDER BY JobCardNum  limit 1