Select nthrow to mth row
-
I have a query like
select empid,empname from emp order by empid
I want to select nth record to mth record (eg: 5th record to 12th record). These two numbers are to be fed using variables. How can i get this query by modifying the above mensioned query ?
-
as i remember, the trick is to select the TOP N of a sub select:
select TOP 12 empid,empname from emp order by empid contains all teh records you want, so you do this:
SELECT TOP 7 * FROM (select TOP 12 empid,empname from emp order by empid ASC) X order by empid DESC
that gives you 12 thru 5...sub select yet again to get them back in the order you originally specified
SELECT * FROM (SELECT TOP 7 * FROM (select TOP 12 empid,empname from emp order by empid ASC) X order by empid DESC) Y order by empid ASC
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Remember that SQLServer does not guarantee any physical row order - the execution plan may change as data changes and lead to a different order in the result set unless you specify it explicitly with an ORDER BY. Accordingly, record numbers only exist if you have defined them, eg by including an identity column in the source table(s).
-
??? I'm thinking that Lowell covered that pretty well...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
.. and I'm thinking you're right Jeff ! I didn't mean to imply anything otherwise.
As long as EmpID is unique (presumably it is) and that is the selection order required then no problem. In the generic case, however, developers often think in terms of physical record orders that simply don't exist.
-
My point is that let us assume there are 500 rows in the result of the query which is written in a stored procedure. But i want to get records in chuncks of 50 each. That is at the first call i should get records 1 to 50. Next execution of the stored procedure 51 to 100 and so on. The stored procedure an have parameters that can specify the beginning number and end number. But how the query is to be modified ? I think now it is more clear to you, experts.
-
Ah... got it... especially the part about developers thinking that data is stored in a physical order... thought you were just being obvious and that's not the case at all. Thanks for the feedback, Stewart.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lowell covered that... you just need to modify the numbers in the "row 5 through row 12" example you asked for and he gave. If you are using SQL Server 2000, you will need to do this as dynamic SQL because TOP is not programmable and SET ROWCOUNT affects whole queries and cannot be set in a sub-query.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's another approach. Create a table variable that will contain an Identity value and the PK field/s of your table.
example
Declare @rs Table (
RecNo int Identity Not Null,
EmpID int,
Primary Key (RecNo))
Populate the table variable by selecting the set of (say 500) records that interest you. Ensure that you order the resultset accordingly.
eg.
Insert @rs
Select EmpID from employee where Name In ('Jack', 'Jill') Order By Name
Then you can pull back the nth to mth records using a join.
eg:
Select
e.*
From
Employee e
Inner Join
@rs r
On
e.EmpID = r.EmpID
Where r.RecNo Between @n And @m
-
Not a bad approach but have you tried it on, say, a million rows? Makes a real busy server if you have 2000 connections trying to do the same thing
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
in my case, I have a search function that does the top 50 similar to what you were asking for///however when i need the next 50, i make sure the application passes back the highest ID i had previously used....
then i can use SELECT top 50 * FROM SOMETABLE WHERE SOMETABLEID > @LASTID ORDER BY SOMETABLEID
that allows a million row table to use the PK effeciently, exactly like Jeff is suggesting...
some minor changes to your logic could do the above, just by making sure the highest ID used is passed back to the procedure.
something like this:
SELECT * FROM (SELECT TOP 7 * FROM (select TOP 12 empid,empname from emp WHERE EMPID > @LASTEMPID order by empid ASC) X order by empid DESC) Y order by empid ASC
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
SELECT *
FROM TableName
ORDER BY ColumnName ASC
OFFSET 40 ROWS
FETCH NEXT 10 ROWS ONLY
will get 40th row to 50th row
Chandrasekhar
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply