SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A tricky one


A tricky one

Author
Message
Matt-1034261
Matt-1034261
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 305
Hi

I 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?

Thanks
Matt
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3711 Visits: 6512
Since 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.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Matt-1034261
Matt-1034261
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 305
Thanks Adi, I'll give that a go
5409045121009
5409045121009
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 675

CREATE TABLE tmp(iCol INT identity(1,1), some_col varchar(10))
GO
INSERT tmp(some_col) SELECT 'a'
GO 100
DELETE FROM tmp
WHERE iCol % 3 = 0
GO
SELECT TOP 5 *
FROM tmp ORDER BY iCol Desc
GO
WITH cte(a) AS (
SELECT TOP 5 iCol FROM tmp ORDER BY iCol Desc)
SELECT TOP 1 a FROM cte ORDER BY a
GO
SELECT TOP 1 f.iCol
FROM (
SELECT TOP 5 iCol FROM tmp ORDER BY iCol Desc) f
ORDER BY f.iCol
GO
DROP TABLE tmp
GO





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search