I want to select the nth row from a table. But as of now I do not know how to hatch the query for it. I have the logic.
1. Select the top n records (record set 1)
2. Select the top n-1 reocrds (record set 2)
3. select from the table where records lies in record set 1, but now in record set 2.
This logic isolates the nth record. How do you write the query for it?
If you are using SQLServer 2005 then you could use the Row_Number
if you are using SQL Server 2000 then you can try to use the ROWCOUNT to do this
give it a try
I've tried using the row count like this:
But this variable holds the value of the number of records processed in the previous operation. So if you execute a select command to query a table, and after that you execute the above statement you will see the number of records that was actually returned...
PS: Could you tell me the meaning of paging...
You can do a
SET RowCount 10
instead of doing a SELECT RowCount.
SET RowCount 10
SELECT * from Table
use SET ROWCOUNT to just get the next n records after (and including)
Paging means that you get XX rows from a table at postion YY.
To get record n, then that could look like get page at postition
N with a page size of 1.
You can also read
: Hi read http://scottonwriting.net/sowblog/posts/5992.aspx
Ok thanks, I'll post back with my ideas later...Btw could you tell me what rowcount is...?
TOP vs. SET ROWCOUNT
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in the following ways:
The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is ended when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement that it is specified in. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.
SQL Server 2000
1. SELECT TOP 10 * from Table1
1. SELECT TOP 9 * from Table2
3. SELECT * FROM Table1 WHERE ID NOT IN
(SELECT ID from Table2)
>>This logic isolates the nth record. How do you write the query for it?
Can you not just select a specific ID? or is the ID range not complete?
SELECT * FROM table2 WHERE ID=nnnn?
Selecting the absolute Nth record regardless of ID is a bit more complex.
One way is to;
You could select the N first rows in the table using TOP and store it into a temporary table. Then select the last one from that table like
like (psuedo code)
SELECT top N ID from Table1 INTO Temptable /* Just store ID records in temptable */
SELECT * FROM Table1 WHERE ID IN
(SELECT TOP 1 ID From TempTable ORDER BY ID DESC)
See help on how to create a temporary tabl
e. using temporary Table might not be the best if N is very large.
Hope this helps.
: 1. SELECT TOP 10 * from Table1
: 1. SELECT TOP 9 * from Table2
: 3. SELECT * FROM Table1 WHERE ID NOT IN
: (SELECT ID from Table2)
The problem is that there may be duplicate IDs. So in that case somehow a select distinct should work. The idea is that if there are duplicates and one of them happens to lie in the row we are fetching, then show only one record! If you are considering the subject of duplicates there may be two or more records that are same...
And the rule is you can't use temporary tables :-(
I don't think there is a query-only solution for this. However the 'set rowcount' thing works for the moment.
Well the story behind why I ask this particular task is my senior gives me interesting challenges. For one of the challenges, I misunderstood one of the question. And the result of that was this question, selecting the nth row...