CODEWITHSUNDEEP
mysqlsql
Connor Bishop
Connor Bishop

Reputation: 981

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

Upvotes: 2

Views: 68

Answers (3)

Robert Columbia
Robert Columbia

Reputation: 6418

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

Upvotes: 2

jtrautmann
jtrautmann

Reputation: 96

Instead of setting JobCardNum = $num you could say that JobCardNum should be greater than or equal $num:

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

This will return a list of all data with a minimum of $num forJobCardNum. As you just want one result, you could limit it to 1:

"SELECT * FROM JobCards WHERE JobCardNum >= $num ORDER BY 'JobCardNum' asc LIMIT 1"

Upvotes: 1

Tom H
Tom H

Reputation: 47402

If you just want the JobCardNum then this is valid in all SQL dialects:

SELECT MIN(JobCardNum) AS JobCardNum
FROM JobCards
WHERE JobCardNum >= $num

For MySQL you can use this to get all of the columns:

SELECT
    JobCardNum,
    SomeOtherColumn,
    ... <list the columns, never use SELECT *>
FROM
    JobCards
WHERE
    JobCardNum >= $num
LIMIT 1

If you wanted to use ANSI SQL, rather than rely on MySQL's LIMIT:

SELECT
    JobCardNum,
    SomeOtherColumn,
    ... <list the columns, never use SELECT *>
FROM
    JobCards JC
WHERE
    JobCardNum = (SELECT MIN(JobCardNum) AS JobCardNum FROM JobCards WHERE JobCardNum >= $num)

Upvotes: 1

Related Questions