Row numbers

[epsmith81]

Hi,

I am developing a .NET application in C#, and have a table of history records
that I wish to present to the uses a series of pages navigable in browser-like fashion (next/back, gotopage). Since the number of records in this history is predictably
large, I am looking at ways not to maintain the entire collection in my app's memory, and
only have loaded the viewed data on the current page into app memory.

I am currently looking into using ADO.NET classes along with Microsoft Jet SQL 4.0
to grab data from a single table in an Access database as needed. Not sure if this
is overkill for a single table, but since I needed random access, and wanted the
records to be of varying size I thought this would be easier than using a file.

I have encountered the following problem:

While I can get the entire number of records in the table via

SELECT COUNT(*) FROM MYTABLE

I need a way to retrieve records n -> N where n and N are the start and end
row positions that I wish to present in the current page (perhaps after I have sorted the
table in some order other than chronological). I am placing this on the SQL side, because
I want the DataSet->DataTable that I create via ADO.Net to only contain the records
n -> N.

The only way I have seen of doing this is to create a new column based on the
order of the table: (using the example of an order table)

SELECT *
FROM a =
SELECT
(SELECT COUNT(*)
FROM dbrders AS O2
WHERE O2.orderid <= O1.orderid) AS rownum,
orderid, CONVERT(varchar(10), orderdate, 120) AS orderdate,
empid, custid, qty
FROM dbrders AS O1
ORDER BY orderid
WHERE a.orderID BETWEEN n and N

However this seems like it would be very slow. I have noted that other
SQL dialects like T-SQL support functions like IDENTITY, or ROW_NUMBER
(i think in the latests). Is there any such support in Jet SQL 4.0?

 

[JonFer]

No support for ROW_NUM that I know of.

You can compare the performance of your query against a self-join to see which is faster.

Select a.ID, Count(b.ID) as RowNum
From table1 as a, table1 as b
Where b.ID<=a.ID

John

http://www.interautomateconsulting.com

 

[BNPMike]

You might of course try MySQL instead. It natively supports the idea of Select Rows 200 to 240 of a query.

For Jet you can easily add a row number. One option is to create a temporary table with an Autonumber field.

 

[epsmith81]

Great! Thanks for the info.