A SQL query
-
I got a table
mytablein SQL server database. The table contains 20 records. If i execute queryselect * from mytable, total 20 records are returned. If i want the first four records, i execute the queryselect top 4 * from mytable, and it returns the first four records. Can anyone tell me about the queries in following cases: 1) Selecting last 4 records from table. 2) Selecting records 9 to 15 from the table. -
I got a table
mytablein SQL server database. The table contains 20 records. If i execute queryselect * from mytable, total 20 records are returned. If i want the first four records, i execute the queryselect top 4 * from mytable, and it returns the first four records. Can anyone tell me about the queries in following cases: 1) Selecting last 4 records from table. 2) Selecting records 9 to 15 from the table.Shamoon wrote: Can anyone tell me about the queries in following cases: 1) Selecting last 4 records from table. 2) Selecting records 9 to 15 from the table. It sounds like you need to page the recordset. If your table contained a primary key that auto-incremented the value this could simply be done by an SQL statement, however this is not normally the case. Check the following: http://www.codeproject.com/asp/rspaging.asp[^] http://www.flws.com.au/showusyourcode/codeLib/code/rstPaging.asp?catID=3[^]
Nick Parker
You see the Standards change. - Fellow co-worker
-
I got a table
mytablein SQL server database. The table contains 20 records. If i execute queryselect * from mytable, total 20 records are returned. If i want the first four records, i execute the queryselect top 4 * from mytable, and it returns the first four records. Can anyone tell me about the queries in following cases: 1) Selecting last 4 records from table. 2) Selecting records 9 to 15 from the table.The answer to both of the above depends on the table design. If the table has a defined primary key, then you could do 1) as select top 4 * from mytable order by mykey ASC/DESC (whether you need ASCending or Descending depends on how you defined the key...) how to get a range (2) is more challenging if the table contains an identity field (need not be part of the primary key), and records in the table never get deleted, you could use the identity as record index: select * from mytable where myindex > 8 and myindex < 16 otherwise, you would need something cumbersome like: select top 6 * from mytable where myidentity not in (select top 8 myidenty from mytable) if there is no identity field, then the notion of nth record is possibly arbitrary and variable over time...