How to retrieve intermediate records using SQL or T-SQL?

• Prashant Chowta

  SSC Enthusiast

  Points: 126

  More actions

  October 13, 2004 at 1:16 am

  #102238

  Hi All,

  I am a new user of SQL SERVER 2000. I have an application where I have to retrieve intermediate records, e.g: If the table has 1000 records , then my select statement should retrieve record between 201 to 300. There are composite primary key(4 columns) defined on the table. Is it possible in SQL SERVER using a single select statement or will i have to use a stored procedure. I have come across the TOP statement i.e. SELECT TOP 300 * FROM table_name. But this statement retrieves top 300 records. Oracle provides this feature of ROWNUM which can be used in the WHERE clause as ROWNUM BETWEEN 201 AND 300. Is there any euivalent to this statement is SQL SERVER 2000.  

  Can anybody help me in this regard.

  Thanks & regards.

  Prashant   Chowta.

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  October 13, 2004 at 5:47 am

  #526492

  If you have an ID of some sort on the table you could do WHERE RecordID BETWEEN 201 AND 300

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Antares686

  SSC Guru

  Points: 125444

  More actions

  October 13, 2004 at 1:19 pm

  #526577

  As long as you are always are sorting on the key fields then try

   

  select top 100 * from (select top 300 * from tblX order by keys asc) tblI Order By keys desc

   

  tblI is an alias for the subquery.

   

  There are some other ways as well including using temp tables within SPs.

• Prashant Chowta

  SSC Enthusiast

  Points: 126

  More actions

  October 14, 2004 at 12:13 am

  #526648

  Thanks AJ & Antares for your prompt help, I will try ur suggestions & let you know.

  Regards

  Prashant Chowta.

   

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  October 14, 2004 at 8:41 pm

  #526865

  Another option:

  select tbl1.* from

  (select top 300 * from tblX Order By keys ) tbl1

  left join (select top 200 keys from tblX Order By keys ) tbl2 on tbl2.keys=tbl1.keys

  where tbl2.col is null

  You can compare performance of the options. It depends on configuration of your server.

   

  But if you really need to do it often and fast you better create new computed column RowNumber and assign value from UDF "MyNumber":

  Select count(*)

  from tblX

  where key1<=@key1 and key2<=@key2 and key3<=@key3 and key4<=@key4

  (Use <= or >= depending on sort order for the key column)

  Formula in cell : = MyNumber(key1, keyl2, keyl3, key4)

  Then you can use your favourite Oracle query.

  If you do not have more than 5 inserts and deletes per second it's is the best solution.

  _____________
  Code for TallyGenerator