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)
);

Github repository about-MSSQL, path: /sql/select/top-n/create-table.sql

… and filled with values:

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

Github repository about-MSSQL, path: /sql/select/top-n/create-table.sql

A simple top n query

The following simple top n query returns 4 records:

select top 4
   id,
   txt
from
   tq84_top_n;

Github repository about-MSSQL, path: /sql/select/top-n/select-top-n.sql

Combining with order by

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;

Github repository about-MSSQL, path: /sql/select/top-n/select-top-n-order-by.sql

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

Using with ties

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;

Github repository about-MSSQL, path: /sql/select/top-n/select-top-n-with-ties-order-by.sql

Paging the record set

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;

Github repository about-MSSQL, path: /sql/select/top-n/select-row-n-through-m.sql

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1788498625, '216.73.217.21', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/SQL-Server/sql/select/top-n/index(128): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78