I want to find the 2nd, 3rd, & 4th rows if avaialble. I have found the
following solution, but doesn't work if there is not a 4th record, it will
return the 3rd record. anybody have a better solution?

This gets the 2nd record.


SELECT TOP 1 *
FROM (SELECT TOP 3 *
FROM t_contactnumber
WHERE ContactNumberTimeID = 0 and ContactNumberTypeID not in
(3,4,11,12) and clientid = 70
ORDER BY ContactNumberID DESC) AS topn
ORDER BY ContactNumberID ASC

This gets the 3rd record, etc. but can duplicate 2nd if no 3rd record
available.

SELECT TOP 1 *
FROM (SELECT TOP 2 *
FROM t_contactnumber
WHERE ContactNumberTimeID = 0 and ContactNumberTypeID not in
(3,4,11,12) and clientid = 70
ORDER BY ContactNumberID DESC) AS topn
ORDER BY ContactNumberID ASC


--
Michael Akin

MS Sql Server LazyDBA home page