InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 12:39:13 GMT20http://www.sqlservercentral.com/Forums/Topic1431972-23-1.aspx[code]CREATE TABLE tmp(iCol INT identity(1,1), some_col varchar(10))GOINSERT tmp(some_col) SELECT 'a' GO 100DELETE FROM tmp WHERE iCol % 3 = 0GOSELECT TOP 5 * FROM tmp ORDER BY iCol DescGOWITH cte(a) AS ( SELECT TOP 5 iCol FROM tmp ORDER BY iCol Desc)SELECT TOP 1 a FROM cte ORDER BY aGOSELECT TOP 1 f.iCol FROM ( SELECT TOP 5 iCol FROM tmp ORDER BY iCol Desc) f ORDER BY f.iCol GODROP TABLE tmpGO[/code]Tue, 19 Mar 2013 02:04:06 GMT5409045121009http://www.sqlservercentral.com/Forums/Topic1431972-23-1.aspxThanks Adi, I'll give that a goSun, 17 Mar 2013 14:20:29 GMTMatt-1034261http://www.sqlservercentral.com/Forums/Topic1431972-23-1.aspxSince there is not such thing as row beneath another row, I assume that you meant that you want the identity value that has 3 different values between it and the current identity value that you are looking at. If I'm correct, then you can have a CTE that selects the top 4 records from the table where the ID value is smaller then the ID of your current row order by the ID desc. From the CTE you can select the min(ID). If you would have written a script with the table structure and insert some test data, I would have written a code that demonstrates that. Another option if to use ranking functions and get the record number that you want.AdiSun, 17 Mar 2013 08:27:47 GMTAdi Cohn-120898http://www.sqlservercentral.com/Forums/Topic1431972-23-1.aspxHiI have a table of links to images. I have an identity key for a particular image but i want to get the identity value of the image 4 rows beneath it using the current identity value. Is there an SQL statement that could do this?ThanksMattSun, 17 Mar 2013 05:06:35 GMTMatt-1034261