Finding out a record
-
Hello,
I would like to know how to find out the nth row of a data table.
For example, I have a Customer table and it has about 90,00 rows. I just want to know the CustomerID of 6,500th row. How do I find out it in fastest way? Even we can use like "Select Top 6500 CustomerID From Customer Where...", I feel it's not good for performance. Do you have any a better way to find it out?
Thank you.
-
Here's 1 idea:
This selects every 6500 th record, using an identity field
SELECT fielda, fieldb, fieldc
FROM TabRele_Name
WHERE Identityfield % 6500 = 0
This selects JUST the 6500 th record, using the identity field
SELECT top 1 fielda, fieldb, fieldc
FROM TableName
WHERE identityfield % 6500 = 0
I'm not sure how to do it without an identity field, but I'm sure all the SQL wizards around here will have more ideas.
-
Thanks for reply. But I do not have any identity field that can be devide by interger or whatever. My Primary Key is CustomerID which is nvarchar(20). Hope this way could be a good idea for those who using numeric types. Thank you anyway. Hope someone can help me out soon. It's urgent.
-
maybe the quickest way would be to:
set rowcount 6500
Select Top 1 CustomerID From Customer order by CustomerID DESC
**ASCII stupid question, get a stupid ANSI !!!** -
Here's another (similar) way - I wrote it to run on the orders table in Northwind and it returns the tenth row, based on order ID:
select top 1 a.* from (select top 10 * from orders order by orderid) a order by orderid desc
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Does it have to be every 6500th row or can it be any rows?
Select top 6500 * from table order by newid().
I'll post other solutions tomorrow.
-
Thank you Phil and Remi.
Remi, it can be any rows up to the user. I have a huge table file and am using my custom paging control. I need the performance for that. That's why. It can be any variable row number. Something like a user limit the row in the gride to 20. And user selected page number 11 and pressed "Go" button. That is the example on what I"m trying to do. If user select page 10, I need to know the record of 9 * 20 = 180 + 1 = 181. Anyway, what I'm trying to say is that row number is variable. Thanks.
-
Paing,
If I understand your question, the following discussion is just what you need.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=132028
-
Paing I reread your last post, noticing the 'large table' comment. If you have a large table and performance is an issue, you probably need a numeric (identity) column for your task.
First question would be whether the order of records will be constant, or will the user decide the order? If it stays the same and the order in which the records are entered is acceptable, add a column to the table just for this. Set the Identity attribute and index the column.
There are a variety of ways of solving your problem. The larger question is how your data will be fetched by the front end;
Order of records constant, or a limited number order types?
Will user usually fetch all records or just a few pages each time?
Will the data be filtered?
If the need is urgent as you say, you probably just need a 'good enough' solution for now. However if possible (and if this is an important application) some time should be spent on optimizing the solution. With a deeper description of the app, we may be able to provide a better solution.
-
For paging, I am using the following method. It's efficient and flexible.
1) the SP needs to have parameters (@PK_Column_Parameter) for the PK coloumns of the paging data, e.g. @CustomerID.
2) the SP needs to have the parameter for row numbers in a page @RowInPage
3) the SP needs to have the parameter for page turinning direction @Direction
4) the SP always returns one more row than requested by @RowInPage to indicate there are more rows available. However the client App does not show the extra row. If rows equal to or less than @RowInPage is returned, it means there is no more data. The client App needs to disable corresponding button.
If the @Direction is "Next page", @PK_Column_Parameter should be the PK of the last reecord in the current page.
If the @Direction is "Previous page", @PK_Column_Parameter should be the PK of the first reecord in the current page.
in the SP:
SET @Rows=@RowInPage+1
SET ROWCOUNT @Rows
IF @Direction>0 -- next page
BEGIN
SELECT [col_list]
FROM [table_name]
WHERE <A href="mailto
K_Column@PK_Column_Parameter">PK_Column>@PK_Column_ParameterORDER BY <A href="mailto
K_Column@PK_Column_Parameter">PK_Column ASCEND
ELSE
BEGIN
SELECT [col_list]
FROM [table_name]
WHERE <A href="mailto
K_Column@PK_Column_Parameter">PK_Column<@PK_Column_ParameterORDER BY <A href="mailto
K_Column@PK_Column_Parameter">PK_Column DESCEND
I am typing this from my memory so the code maybe not accurate. You can get the idea, right?
-
A Cursor and fetch relative command may work best, especially if you may be sorting the records by different fields.
For example:
create spRecordNo @rcdno int
declare @CustId varchar(20)
Declare tmpCursor Dynamic for (Select CustID from tblCustomer order by CustomerName)
Open tmpCursor
Fetch Relative @rcdno from tmpCursor into @CustID
close tmpCursor
deallocate tmpCursor
Select top 10 * from tblCustomer
where CustomerID>@CustID
order by CustomerName
-
Hi jcobble,
Thank you. But my sort order will be used only for PK. Not on other fields. I'm afraid I feel using cursor would slow down the performance as we will be using a larg table. Thank you.
-
Hi Johnson and peterhe,
Thank you for the codes peterhe. I have that done in my datalayer.
John. Here are my answers to your questions.
Order of records will be decided by me. But I change the order ASC and DESC according to the program flow. Here is my code for your reference.
Select Top 20 * From Customer Where CustomerID > @LastCustomerID Order by CustomerID ASC
Select Top 20 * From Customer Where CustomerID < @TopCustomerID Order By CustomerID DESC
(Then I use DataView.Sort feature to sort the order of records back.)
All records will be limited to 20 per request. 'Coz I am using only 20 record(s) in my page control.
My problem here is that when user select the page number 70 or something like that, I need to show associated records for that page number. So I do not know what will be the fastest way to find out the very first PK for that. If we will use Top statement, our sql statement will be like this.
Select Top 1399 CustomerID From Customer Order By CustomerID
I need to know the page number 69's last CustomerID so that I can get the starting CustomerID for page number 70. Hope I explained well enough to understand what I'm trying to do.
Thank you all.
-
For randomly jumping from page to page, I can't figure out a satisfied solution in SQL 2000, which does not have a native row_number column for records in a table.
What you could do is to search the target page based on the current page PK and current page number. The pesuo-code is like:
SET @Row=(@CurrentPgNumber-@TargetPgNumber)*@RowsPerPage
IF @Row<0 SET @Row=0-@Row
SET ROWCOUNT @Row
IF @CurrentPgNumber>@TargetPgNumber
INSERT INTO #TempTable
SELECT CustomerID FROM table WHERE CustomerID<@Current_PK ORDER BY CustomerID DESC
ELSE
INSERT INTO #TempTable
SELECT CustomerID FROM table WHERE CustomerID>@Current_PK ORDER BY CustomerID DESC
( Or you can use TOP and ORDER BY in dynamic T-SQL )
Then you can return the target page.
-
Thank you. That would be the best solution for the moment. I will do that. Much appreicate everyone.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply