Search notes:

SQL Server: top n queries

Creating a test table

First a test table is created …

create table tq84_top_n (
  id  int primary key,
  txt varchar(11)
);

… and filled with values:

create table tq84_top_n (
  id  int primary key,
  txt varchar(11)
);

The following simple top n query returns 4 records:

select top 4
   id,
   txt
from
   tq84_top_n;

Usually, a top n query should be combined with an order by clause:

The following query returns exactly four records, as specified with the top 4 clause:

select top 4
   id,
   txt
from
   tq84_top_n
order by
   txt;

However, this might not be what someone wants as there are three records whose txt value is def ghi.

The following query returns five records (all three txt = 'def ghi' records are now included).

select top 4 with ties
   id,
   txt
from
   tq84_top_n
order by
   txt;

The offset n rows fetch next m rows allows to page through the record set:

select
   id,
   txt
from
   tq84_top_n
order by
   id
offset     5 rows
fetch next 4 rows only;