| Author |
 Topic  |
|
rlconkl
Starting Member
USA
1 Posts |
 Posted - 02/24/2005 : 16:48:49
|
What about pushing the "paging" off on the SQL Server, rather than the client? For example, to get the third page of fifty records, use a subquery in this method instead:
SELECT TOP 50 * FROM (
SELECT TOP 150 *
FROM your_table
ORDER BY some_field DESCENDING
)
ORDER BY some_field ASCENDING
The value 150 is the page number (3) multiplied by the page size (50). I'm not sure how its performance would compare to the correlated subquery options presented earlier. |
 |
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 02/24/2005 : 17:56:11
|
Great concept!
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine! |
|
|
|
mwatts
Starting Member
1 Posts |
Posted - 06/09/2005 : 11:00:30
|
The problem with rlconkl's solution is that as you get deeper into the recordset, the processing becomes more and more intensive.
I've tried using cursors, correlated subqueries but they all suffer from the performance hit associated with having to retrieve a large recordset then "trim" it down to the correct bits and a pieces.
An idea I thought of, but haven't tried (not even sure if it's possible, but you never know) is to create a trigger on a table so that every time a record is added or deleted it drops and recreates an identity field with an increment of 1.
This would mean you couldn't sort the data on the server, but you could in your code (assuming you're using a nice compiled language).
Still messy and not the best solution, but until MS give us a LIMIT statement...  |
|
|
|
bitm.neeraj
Starting Member
2 Posts |
Posted - 10/02/2007 : 08:10:26
|
Hi Friends,
Need some help here. I am new to SQL Server and need of a query that can return me rows based on some row number.
I have a simple table as Products with 3 columns as Id(Type: Text), Product_Id(Type: Text) and Product_Desc(Type: Text).
The data in this table is huge (around 1.5 million records). I need to fetch all the data one after other but only 1000 records at a time. Can someone please help me out with this query. I tried following:
"Select Row_Number() Over(Order By Products.ID) as rowNumber, Products.Id, Products.Product_ID, Products,Product_desc from Products where rowNumber between 1000 and 2000 order by id"
This is not working and saying some parameter missing in Over syntax.
Please help. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29779 Posts |
Posted - 10/02/2007 : 08:15:08
|
You must use SQL Server 2005 to use ROW_NUMBER() function.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
bitm.neeraj
Starting Member
2 Posts |
Posted - 10/02/2007 : 08:59:59
|
I am actually using Microsoft Access Database. Any ideas as how I can acheive it in MSAccess.
Thanks |
|
|
|
Alharo
Starting Member
Mexico
2 Posts |
Posted - 01/06/2012 : 16:53:40
|
quote:
Originally posted by Anonymous
Doing it without a temp table <P>You can do it with a subquery like so:
<code>
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname
</code>
Not that I know any good reason to do this (I doubt it's more efficent) but hey, it works.
This worked to me EXCELENT! Thanks!
May the Force be with you |
|
|
|
Alharo
Starting Member
Mexico
2 Posts |
Posted - 01/06/2012 : 16:55:31
|
quote:
Originally posted by Anonymous
Doing it without a temp table <P>You can do it with a subquery like so:
<code>
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname
</code>
Not that I know any good reason to do this (I doubt it's more efficent) but hey, it works.
This worked to me EXCELENT! Thanks!
May the Force be with you |
|
|
|
Topic |
|
Article: Returning a Row Number in a Query
