Select MID?? instead of TOP
-
Is there a way instead of using SELECT TOP 10 to say something like: SELECT MID 11 10 or something, so it starts at the 11th record and takes 10 records after it. This way I can easily put my data into pages, straight from the database, with 10 records on each page. I would also need to be able to first order them and then take 10 records starting from some nth record. Any suggestions, returning 2000 records when i only display 10 seems very wastefull. Thanks. http://www.codeproject.com/aspnet/custompaging.asp I found this link here, but this seems very inefficient as well. It creates a temp table and still selects all records and inserts them into the temp table. Regarding this way... does the # in front of the table name mean it gets dropped when the stored procedure is done running? The Author never drops it, and so if multiple people called this function at the same time, what happens to the temp table? Never seen the pound sign # in front of a table before. Thanks some more.
-
Is there a way instead of using SELECT TOP 10 to say something like: SELECT MID 11 10 or something, so it starts at the 11th record and takes 10 records after it. This way I can easily put my data into pages, straight from the database, with 10 records on each page. I would also need to be able to first order them and then take 10 records starting from some nth record. Any suggestions, returning 2000 records when i only display 10 seems very wastefull. Thanks. http://www.codeproject.com/aspnet/custompaging.asp I found this link here, but this seems very inefficient as well. It creates a temp table and still selects all records and inserts them into the temp table. Regarding this way... does the # in front of the table name mean it gets dropped when the stored procedure is done running? The Author never drops it, and so if multiple people called this function at the same time, what happens to the temp table? Never seen the pound sign # in front of a table before. Thanks some more.
-
How about: SELECT TOP 10 field1, field2 ORDER BY field1 WHERE field2 IS BETWEEN 'VALUE1' AND 'VALUE2' ...hey that could even be a stored procedure, where you pass VALUE1 and VALUE2 as params...
but i have no idea what value1 and value2 would be. Maybe I have an auto-increment PK but that won't work because if some rows get deleted, then you dont have a range of 10 records knowing that a multiple of 10 records was skipped. This wont work. I modified my question, see the bottom of the original question, i added an example i found. Thanks anyways.
-
but i have no idea what value1 and value2 would be. Maybe I have an auto-increment PK but that won't work because if some rows get deleted, then you dont have a range of 10 records knowing that a multiple of 10 records was skipped. This wont work. I modified my question, see the bottom of the original question, i added an example i found. Thanks anyways.
Doing a SELECT TOP N ... without a WHERE and ORDER BY clause is kind of trivial, and you shouldn't rely on an id or auto-increment field in an ORDER BY, as it is no guarantee of the chonological order in which records are added to a table. You should work with other fields, such as datetime fields, foreign key fields, etc. Another question: Are you referring to paging through a DataSet using ASP.NET? If so, there are plenty of examples and articles out there on data paging and controls like the DataGrid control.