SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Returning a Row Number in a Query
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

rlconkl
Starting Member

USA
1 Posts

Posted - 02/24/2005 :  16:48:49  Show Profile  Visit rlconkl's Homepage  Reply with Quote
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.
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 02/24/2005 :  17:56:11  Show Profile  Reply with Quote
Great concept!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

mwatts
Starting Member

1 Posts

Posted - 06/09/2005 :  11:00:30  Show Profile  Reply with Quote
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...
Go to Top of Page

bitm.neeraj
Starting Member

2 Posts

Posted - 10/02/2007 :  08:10:26  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 10/02/2007 :  08:15:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You must use SQL Server 2005 to use ROW_NUMBER() function.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bitm.neeraj
Starting Member

2 Posts

Posted - 10/02/2007 :  08:59:59  Show Profile  Reply with Quote
I am actually using Microsoft Access Database. Any ideas as how I can acheive it in MSAccess.

Thanks
Go to Top of Page

Alharo
Starting Member

Mexico
2 Posts

Posted - 01/06/2012 :  16:53:40  Show Profile  Reply with Quote
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
Go to Top of Page

Alharo
Starting Member

Mexico
2 Posts

Posted - 01/06/2012 :  16:55:31  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000