Skip to content

Ordering and Paging

ORDER BY

Add one or more sort expressions with .OrderBy(). Columns sort ascending by default.

var sql = Sql
    .Select("Id", "UserName", "Email", "Age")
    .From("Users")
    .Where(SqlExpression.GreaterThanOrEqual("Age", 18))
    .OrderBy("UserName")
    .ToSql();

SELECT [Id], [UserName], [Email], [Age] FROM [Users] WHERE [Age] >= 18 ORDER BY [UserName] ASC

Descending order

Pass SqlSortOrder.Descending as the second argument:

var sql = Sql
    .Select("Id", "Name")
    .From("Users")
    .OrderBy("Id", SqlSortOrder.Descending)
    .ToSql();

SELECT [Id], [Name] FROM [Users] ORDER BY [Id] DESC

Multiple columns

Chain multiple .OrderBy() calls to sort by several columns:

SqlTable u = new("Users", "u");

var sql = Sql
    .Select(u + "Id", u + "UserName")
    .From(u)
    .OrderBy(u + "UserName")
    .OrderBy(u + "Id", SqlSortOrder.Descending)
    .ToSql();

SELECT [u].[Id], [u].[UserName] FROM [Users] [u] ORDER BY [u].[UserName] ASC, [u].[Id] DESC

Ordering by aggregate

You can order by an aggregate function — useful after GROUP BY:

var sql = Sql
    .Select("Department", SqlAggregate.Count("Id", "Headcount"))
    .From("Employees")
    .GroupBy("Department")
    .OrderBy(SqlAggregate.Count("Id"), SqlSortOrder.Descending)
    .ToSql();

SELECT [Department], COUNT([Id]) AS [Headcount]
FROM [Employees]
GROUP BY ([Department])
ORDER BY COUNT([Id]) DESC

DISTINCT

Call .Distinct() immediately after .Select():

var sql = Sql
    .Select("City", "Country")
    .Distinct()
    .From("Users")
    .ToSql();

SELECT DISTINCT [City], [Country] FROM [Users]

.Distinct() works together with WHERE, ORDER BY, and LIMIT:

var sql = Sql
    .Select("City")
    .Distinct()
    .From("Users")
    .Where(SqlExpression.GreaterThanOrEqual("Age", 18))
    .OrderBy("City")
    .ToSql();

SELECT DISTINCT [City] FROM [Users] WHERE [Age] >= 18 ORDER BY [City] ASC

Paging (LIMIT / OFFSET)

Add .Limit(offset, count) after .OrderBy() to page through results. The SQL produced depends on the active dialect.

Skip and take

var sql = Sql
    .Select("Id", "Name")
    .From("Users")
    .OrderBy("Id")
    .Limit(20, 10)   // skip 20 rows, take 10
    .ToSql();

SELECT [Id], [Name] FROM [Users] ORDER BY [Id] ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY

SELECT "Id", "Name" FROM "Users" ORDER BY "Id" ASC LIMIT 10 OFFSET 20

SELECT `Id`, `Name` FROM `Users` ORDER BY `Id` ASC LIMIT 20, 10

Take first N rows

Pass null for the offset to take from the beginning:

var sql = Sql
    .Select("Id", "Name")
    .From("Users")
    .OrderBy("Id")
    .Limit(null, 5)   // take 5 rows from the beginning
    .ToSql();

SELECT [Id], [Name] FROM [Users] ORDER BY [Id] ASC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY

SELECT "Id", "Name" FROM "Users" ORDER BY "Id" ASC LIMIT 5

SELECT `Id`, `Name` FROM `Users` ORDER BY `Id` ASC LIMIT 5

Practical pagination example

A typical "page N of results" pattern:

int pageNumber = 3;
int pageSize = 25;
int offset = (pageNumber - 1) * pageSize;

var sql = Sql
    .Select("Id", "Name", "Email")
    .From("Users")
    .Where(SqlExpression.Equal("IsActive", 1))
    .OrderBy("Name")
    .Limit(offset, pageSize)
    .ToSql();

SELECT [Id], [Name], [Email] FROM [Users] WHERE [IsActive] = 1 ORDER BY [Name] ASC OFFSET 50 ROWS FETCH FIRST 25 ROWS ONLY

SELECT "Id", "Name", "Email" FROM "Users" WHERE "IsActive" = 1 ORDER BY "Name" ASC LIMIT 25 OFFSET 50

Note

SQL Server requires ORDER BY to be present when using OFFSET/FETCH. The SqlServerDialect enforces this by automatically supplying OFFSET 0 when no offset is given.

Dialect paging summary

Dialect Syntax
Default / SQL Server OFFSET n ROWS FETCH FIRST n ROWS ONLY
PostgreSQL LIMIT n OFFSET n
MySQL LIMIT offset, count

See SQL Dialects for more dialect-specific behaviour.