Query

Hi,

I have a table called Strategic Programmes which has 2 columns "ProgNo" (unique ID) and "ProgName"

I need to connect to SQL Server from Access using connection and recordset objects to find the next highest "ProgNo"

What is the SQL query I need to execute to find the next highest ID number and how do I extract this from my recordset?

Thanks,

Mark

Track:

This query will work both for SQL Server 2000 and SQL Server 2005:

select max (progNo)
from ( select top 2
progNo
from [Strategic Programmes]
order by progNo
) a

Note that it will perform better if the table is either keyed or indexed on the progNo column.

|||

If you are looking for "second" highest, above solutions works perfect.

But if you are looking for "next" highest, following is my way:

select
nextHighestProgNo = max(ProgNo)
from
YourTable
where
ProgNo < CurrentProgNo

If you are currennt ProgNo is 15 and if you execute above query by replaceing CurrentProgNo with 15, you will get the next highest ProgNo. (will be 14 if there is one)

sql