Answers to an SQL interview question

Question:

Write an SQL statement:

Retrieve the 31st to 40th records in Table A (sqlserver, using the Automatically increasing ID as the primary key, note: the ID may not be consecutive .)

 

Answer (tested ):

1. Assume that the ID is continuous:

Select top10 *FromAWhereIDNot in(Select Top30 IDFromA)

Or

Select*FromAWhereIDBetween31And40

 

2. Assume that the ID is discontinuous:

Select top40 *FromAExcept Select Top30 *FromA

Or

Select top10 *FromAWhereId> (Select Max(ID)FromAWhereIDIn(Select top30 IDFromA ))

Or

Select top10 *FromAWhereIDNot in(Select Top30 IDFromA)