Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A tricky one Expand / Collapse
Author
Message
Posted Sunday, March 17, 2013 5:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:44 AM
Points: 93, Visits: 299
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
Post #1431972
Posted Sunday, March 17, 2013 8:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 2,112, Visits: 5,492
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/
Post #1431984
Posted Sunday, March 17, 2013 2:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:44 AM
Points: 93, Visits: 299
Thanks Adi, I'll give that a go
Post #1432024
Posted Tuesday, March 19, 2013 2:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 90, Visits: 523
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




Post #1432517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse