------------------------------------------
DECLARE @MyTable TABLE
(
	[Value]		NVarChar(25)
				Primary KEY				
)
---------------------------------------------
INSERT INTO @MyTable VALUES ('Peter Larsson');
INSERT INTO @MyTable VALUES ('Joe Celko');
INSERT INTO @MyTable VALUES ('Dr.Codd');
INSERT INTO @MyTable VALUES ('C.J Date');
----------------------------------------
SELECT * FROM @MyTable
ORDER BY SubString([Value],2,1)
/*
Value
-------------------------
C.J. Date
Peter Larsson
Joe Celko
Dr.Codd
*/
-----------------------------------------
DECLARE MyCursor CURSOR
	KEYSET
	FOR SELECT * FROM @MyTable
		ORDER BY SubString([Value],2,1)

OPEN MyCursor
FETCH ABSOLUTE 2 FROM MyCursor
CLOSE MyCursor
DEALLOCATE Mycursor
-----------------------------------------
SELECT *
FROM
(
	SELECT  *,
		  ROW_ID=ROW_NUMBER() OVER(ORDER BY SubString([Value],2,1))
	FROM @MyTable
) AS D
WHERE ROW_ID=2;
------------------------------------------
SELECT  *,
	  ROW_ID=IDENTITY(int,1,1)
INTO #tempTable
FROM @MyTable
ORDER BY SubString([Value],2,1)

SELECT *
FROM #tempTable
WHERE ROW_ID=2

DROP TABLE #tempTable
--------------------------------------------
SELECT TOP 1 *
FROM
(	
	SELECT TOP 2 * FROM @MyTable
	ORDER BY SubString([Value],2,1) ASC
) AS D
ORDER BY SubString([Value],2,1) DESC

quote:

---

Originally posted by ms65g

Hi all,
now, I am NOT writing a wrong simple article about "retrieve a specific row from a result set".
And I do not want you post any idea or link to other similar article about it.
Consider following table and result. I want to retrieve only row number 2 ('Peter Larsson') in result set.
A simple method is using CURSOR with FETCH ABSOLUTE N.
And when the SELECT STATEMENT has an ORDER cause then another method is using ROW_NUMBER with OVER cause.
And a bad solution is using IDENTITIY INTO () function, it is bad because the inserting is an expensive job for system.
And when the Query has a ORDER then a very simple method is using two TOP.

---

quote:

---

Originally posted by ms65g

Hi all,
now, I am NOT writing a wrong simple article about "retrieve a specific row from a result set".

---

quote:

---

Yes you are. You are writing an article where the premise of how to address a particular problem is wrong when your tool is a relational database.

---