SQL query troubles: SELECT [TOP x STARTING AT n]
-
I need to be able to do the following using an SQL statement:
SELECT TOP 25 * FROM [Table] WHERE [Condition] ORDER BY [Column]Except I want to be able to specify the start position, i.e. start n rows into the table and then extract the 25 rows I need. I don’t know how many rows will be in the table, and there is no field that has any form of incremental value (so I can’t just say[Condition] = [Table].[Column] > n). The table might contain many thousands of rows, so it is unfeasible to retrieve them all then ignore the one’s I don’t want. So, I guess my question is how can I retrieve x rows from the table, starting from row n, based on the results of my ORDER BY clause. This must be possible, surely? David Wulff dwulff@battleaxesoftware.com -
I need to be able to do the following using an SQL statement:
SELECT TOP 25 * FROM [Table] WHERE [Condition] ORDER BY [Column]Except I want to be able to specify the start position, i.e. start n rows into the table and then extract the 25 rows I need. I don’t know how many rows will be in the table, and there is no field that has any form of incremental value (so I can’t just say[Condition] = [Table].[Column] > n). The table might contain many thousands of rows, so it is unfeasible to retrieve them all then ignore the one’s I don’t want. So, I guess my question is how can I retrieve x rows from the table, starting from row n, based on the results of my ORDER BY clause. This must be possible, surely? David Wulff dwulff@battleaxesoftware.comI know you can use SET ROWCOUNT to limit the number of effected rows. I'm not sure if there is anything to set the start position. Interesting question though.
-
I need to be able to do the following using an SQL statement:
SELECT TOP 25 * FROM [Table] WHERE [Condition] ORDER BY [Column]Except I want to be able to specify the start position, i.e. start n rows into the table and then extract the 25 rows I need. I don’t know how many rows will be in the table, and there is no field that has any form of incremental value (so I can’t just say[Condition] = [Table].[Column] > n). The table might contain many thousands of rows, so it is unfeasible to retrieve them all then ignore the one’s I don’t want. So, I guess my question is how can I retrieve x rows from the table, starting from row n, based on the results of my ORDER BY clause. This must be possible, surely? David Wulff dwulff@battleaxesoftware.comYou can not do that, sorry. (I have also needed it) - Anders Money talks, but all mine ever says is "Goodbye!"
-
You can not do that, sorry. (I have also needed it) - Anders Money talks, but all mine ever says is "Goodbye!"
I had guessed as much :(. Why oh why did the original designers of SQL not think people would need that functionality? It's common sense if you just want to retrieve, say, 20 rows from a fifty million row table, that you would rather not return the first 49,999,980 only to ignore them. David Wulff dwulff@battleaxesoftware.com