Return a row in a select statement

I have a table tbExample with 100 rows. I want to get the row 29. How can I do that with select statement. Something like this:
SELECT * FROM TBEXAMPLE WHERE POSITIONOFROW = 29
LVL 3
maidinhtaiAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT TOP 1 *  With Ties
From urTable E
LEFT OUTER JOIN
(SELECT TOP 28 * WITH Ties FROM YourTable )T
ON E.primaryKey = T.PrimaryKey
WHERE T.PrimaryKey IS NULL
0
 
peter57rCommented:
Hi maidinhtai,
What database ?
You will appreciate that this type of selection will not produce the same answer every time you run it.

Pete
0
 
maidinhtaiAuthor Commented:
Hi Peter57r,
It's a MS  SQL server database. Everytime I run this select statement, it will return the same result.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Aneesh RetnakaranDatabase AdministratorCommented:
or

SELECT * FROM urTable A WHERE 29 = (SELECT Count(*) FROM urTable B WHERE A.primaryKey = B.PrimaryKey )
0
 
maidinhtaiAuthor Commented:
aneeshattingal,
      What is 'With ties' used for? Why do I have to put it into the select statement
0
 
nguyenvinhtuCommented:
SELECT * FROM TBEXAMPLE A
WHERE (SELECT COUNT(*) FROM TBEXAMPLE B WHERE B.COL <= A.COL) = 29

-- Above code always return line 29 of TBEXAMPLE however many column there are in TBEXAMPLE
-- COL is a column name of TBEXAMPLE, you can choose any
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.