Table of Contents

Sorting Results

This guide covers ordering query results using ORDER BY clauses with the fluent query syntax.

Overview

Sorting controls the order in which rows are returned from a query. Velocity provides sorting capabilities through:

  • OrderClause - Defines a sort operation
  • OrderClauseType - Sort direction (Ascending or Descending)
  • OrderBy() method - Fluent syntax for adding sort operations

Single Column Sorting

Ascending Order (Default)

Sort results in ascending order (A-Z, 0-9, oldest to newest):

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

Table products = schema["products"];

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"]
    ])
    .From(products)
    .OrderBy(products["product_name"], OrderClauseType.Ascending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("product_name"));
    }
}

Generated SQL:

SELECT product_name, price
FROM products
ORDER BY product_name ASC

Descending Order

Sort in descending order (Z-A, 9-0, newest to oldest):

Table products = schema["products"];

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"]
    ])
    .From(products)
    .OrderBy(products["price"], OrderClauseType.Descending);

Generated SQL:

SELECT product_name, price
FROM products
ORDER BY price DESC

Default Sort Direction

When OrderClauseType is omitted, ascending order is used:

Query query = new Query()
    .Select(products["product_name"])
    .From(products)
    .OrderBy(products["product_name"]); // Defaults to Ascending

Multiple Column Sorting

Two Columns

Sort by multiple columns with different directions:

Table customers = schema["customers"];

Query query = new Query()
    .Select([
        customers["country"],
        customers["city"],
        customers["company_name"]
    ])
    .From(customers)
    .OrderBy([
        new OrderClause(customers["country"], OrderClauseType.Ascending),
        new OrderClause(customers["city"], OrderClauseType.Ascending)
    ]);

Generated SQL:

SELECT country, city, company_name
FROM customers
ORDER BY country ASC, city ASC

Note: The order matters! Results are sorted first by country, then within each country by city.

Three or More Columns

Table orders = schema["orders"];

Query query = new Query()
    .Select([
        orders["status"],
        orders["priority"],
        orders["order_date"],
        orders["order_id"]
    ])
    .From(orders)
    .OrderBy([
        new OrderClause(orders["status"], OrderClauseType.Ascending),
        new OrderClause(orders["priority"], OrderClauseType.Descending),
        new OrderClause(orders["order_date"], OrderClauseType.Descending)
    ]);

Generated SQL:

SELECT status, priority, order_date, order_id
FROM orders
ORDER BY status ASC, priority DESC, order_date DESC

Sort hierarchy:

  1. First by status (A-Z)
  2. Then by priority within each status (high to low)
  3. Then by order_date within each priority (newest to oldest)

Sorting by Different Data Types

Numeric Sorting

Numbers sort by value:

Table products = schema["products"];

// Sort by price (cheapest first)
Query query = new Query()
    .Select([
        products["product_name"],
        products["price"]
    ])
    .From(products)
    .OrderBy(products["price"], OrderClauseType.Ascending);

Result:

Widget A - $5.99
Widget B - $12.50
Widget C - $99.99

String Sorting

Strings sort alphabetically (case-insensitive in most databases):

Table users = schema["users"];

Query query = new Query()
    .Select(users["username"])
    .From(users)
    .OrderBy(users["username"], OrderClauseType.Ascending);

Result:

alice
Bob
charlie
David

Date/Time Sorting

Dates and timestamps sort chronologically:

Table orders = schema["orders"];

// Most recent orders first
Query query = new Query()
    .Select([
        orders["order_id"],
        orders["order_date"]
    ])
    .From(orders)
    .OrderBy(orders["order_date"], OrderClauseType.Descending);

Result:

Order #150 - 2024-03-15
Order #149 - 2024-03-14
Order #148 - 2024-03-13

Boolean Sorting

Booleans sort with false (0) before true (1):

Table tasks = schema["tasks"];

// Incomplete tasks first
Query query = new Query()
    .Select([
        tasks["task_name"],
        tasks["is_completed"]
    ])
    .From(tasks)
    .OrderBy(tasks["is_completed"], OrderClauseType.Ascending);

Sorting with Aliases

Sort by aliased columns:

Table products = schema["products"];

Query query = new Query()
    .Select([
        (products["product_name"], "name"),
        (products["unit_price"], "price")
    ])
    .From(products)
    .OrderBy(products["unit_price"], OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        // Access by alias
        string name = row.GetFieldString("name");
        decimal price = row.GetFieldDecimal("price");
        Console.WriteLine($"{name}: ${price:F2}");
    }
}

Sorting with Expressions

Sort by calculated values:

Arithmetic Expressions

using YndigoBlue.Velocity.Model;

Table order_items = schema["order_items"];

// Calculate line total and sort by it
Expression lineTotal = new Expression("line_total", new List<IElement>
{
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
});

Query query = new Query()
    .Select([
        order_items["product_name"],
        lineTotal
    ])
    .From(order_items)
    .OrderBy(lineTotal, OrderClauseType.Descending);

Generated SQL:

SELECT product_name, (quantity * unit_price) AS line_total
FROM order_items
ORDER BY line_total DESC

Function Results

Table users = schema["users"];

// Sort by uppercase username
Upper upperName = new Upper(users["username"]);
upperName.SetAlias("upper_name");

Query query = new Query()
    .Select([
        users["username"],
        upperName
    ])
    .From(users)
    .OrderBy(upperName, OrderClauseType.Ascending);

Sorting with Aggregates

Sort grouped results by aggregate values:

Table orders = schema["orders"];

// Count orders per customer
Expression orderCount = new Expression("order_count",
    new Aggregate(AggregateType.Count, new ArithmeticOperator(ArithmeticType.All))
);

Query query = new Query()
    .Select([
        orders["customer_id"],
        orderCount
    ])
    .From(orders)
    .GroupBy(orders["customer_id"])
    .OrderBy(orderCount, OrderClauseType.Descending); // Most orders first

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Top Customers by Order Count:");
    foreach (Result row in results)
    {
        int customerId = row.GetFieldInteger("customer_id");
        int count = row.GetFieldInteger("order_count");
        Console.WriteLine($"Customer {customerId}: {count} orders");
    }
}

Generated SQL:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC

NULL Handling in Sorting

Default NULL Behavior

NULL values sort differently depending on the database:

Database NULLs in ASC NULLs in DESC
DB2 Last First
MySQL First Last
Oracle Last First
PostgreSQL First Last
SQLite First Last
SQL Server Last First
Teradata Last First 
Table orders = schema["orders"];

// Orders sorted by shipped date
Query query = new Query()
    .Select([
        orders["order_id"],
        orders["shipped_date"]
    ])
    .From(orders)
    .OrderBy(orders["shipped_date"], OrderClauseType.Ascending);

// Unshipped orders (NULL) position depends on database

Handling NULLs Explicitly

To control NULL sorting, filter them separately:

// Get shipped orders only, sorted by date
Filter shippedFilter = new Filter(
    new Criterion<object>(
        orders["shipped_date"],
        ConditionalType.IsNotNull,
        null
    )
);

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["shipped_date"]
    ])
    .From(orders)
    .Where(shippedFilter)
    .OrderBy(orders["shipped_date"], OrderClauseType.Descending);

Combining Sorting with Filtering

Filter Then Sort

Table products = schema["products"];

Filter inStockFilter = new Filter(
    new Criterion<int>(
        products["stock_quantity"],
        ConditionalType.GreaterThan,
        0
    )
);

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"],
        products["stock_quantity"]
    ])
    .From(products)
    .Where(inStockFilter)
    .OrderBy(products["price"], OrderClauseType.Ascending);

Generated SQL:

SELECT product_name, price, stock_quantity
FROM products
WHERE stock_quantity > 0
ORDER BY price ASC

Complex Filter with Multi-Column Sort

Table orders = schema["orders"];

// Active orders sorted by priority then date
Filter activeFilter = new Filter([
    new Criterion<string>(orders["status"], ConditionalType.Equals, "pending"),
    new BooleanItem(BooleanType.Or),
    new Criterion<string>(orders["status"], ConditionalType.Equals, "processing")
]);

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["status"],
        orders["priority"],
        orders["order_date"]
    ])
    .From(orders)
    .Where(activeFilter)
    .OrderBy([
        new OrderClause(orders["priority"], OrderClauseType.Descending),
        new OrderClause(orders["order_date"], OrderClauseType.Ascending)
    ]);

Complete Examples

Example 1: Product Catalog

Table products = schema["products"];

// In-stock products sorted by category, then price
Filter inStockFilter = new Filter(
    new Criterion<int>(
        products["stock_quantity"],
        ConditionalType.GreaterThan,
        0
    )
);

Query query = new Query()
    .Select([
        products["category"],
        products["product_name"],
        products["price"],
        products["stock_quantity"]
    ])
    .From(products)
    .Where(inStockFilter)
    .OrderBy([
        new OrderClause(products["category"], OrderClauseType.Ascending),
        new OrderClause(products["price"], OrderClauseType.Ascending)
    ]);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    string currentCategory = "";
    foreach (Result row in results)
    {
        string category = row.GetFieldString("category");
        string name = row.GetFieldString("product_name");
        decimal price = row.GetFieldDecimal("price");
        int stock = row.GetFieldInteger("stock_quantity");

        if (category != currentCategory)
        {
            Console.WriteLine($"\n{category}:");
            currentCategory = category;
        }

        Console.WriteLine($"  {name} - ${price:F2} ({stock} in stock)");
    }
}

Output:

Books:
  Programming Guide - $29.99 (15 in stock)
  Database Design - $39.99 (8 in stock)
  Web Development - $44.99 (12 in stock)

Electronics:
  USB Cable - $5.99 (50 in stock)
  Wireless Mouse - $19.99 (25 in stock)
  Keyboard - $49.99 (10 in stock)

Example 2: Customer Order History

Table orders = schema["orders"];

// Recent orders for a specific customer
Filter customerFilter = new Filter(
    new Criterion<int>(
        orders["customer_id"],
        ConditionalType.Equals,
        customerId
    )
);

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["order_date"],
        orders["status"],
        orders["total"]
    ])
    .From(orders)
    .Where(customerFilter)
    .OrderBy(orders["order_date"], OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Order History (Most Recent First):");
    Console.WriteLine("=====================================");

    foreach (Result row in results)
    {
        int orderId = row.GetFieldInteger("order_id");
        DateTime orderDate = row.GetFieldDateTime("order_date");
        string status = row.GetFieldString("status");
        decimal total = row.GetFieldDecimal("total");

        Console.WriteLine($"Order #{orderId} - {orderDate:d}");
        Console.WriteLine($"  Status: {status}");
        Console.WriteLine($"  Total: ${total:F2}");
        Console.WriteLine();
    }
}

Example 3: Top Customers by Revenue

Table orders = schema["orders"];

// Calculate total revenue per customer
Expression totalRevenue = new Expression("total_revenue",
    new Aggregate(AggregateType.Sum, orders["total"])
);

Expression orderCount = new Expression("order_count",
    new Aggregate(AggregateType.Count, new ArithmeticOperator(ArithmeticType.All))
);

Query query = new Query()
    .Select([
        orders["customer_id"],
        totalRevenue,
        orderCount
    ])
    .From(orders)
    .GroupBy(orders["customer_id"])
    .OrderBy(totalRevenue, OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Top 10 Customers by Revenue:");
    Console.WriteLine("============================");

    int rank = 1;
    foreach (Result row in results.Take(10))
    {
        int customerId = row.GetFieldInteger("customer_id");
        decimal revenue = row.GetFieldDecimal("total_revenue");
        int orders = row.GetFieldInteger("order_count");

        Console.WriteLine($"{rank}. Customer #{customerId}");
        Console.WriteLine($"   Revenue: ${revenue:N2}");
        Console.WriteLine($"   Orders: {orders}");
        Console.WriteLine();

        rank++;
    }
}

Example 4: Task Priority Queue

Table tasks = schema["tasks"];

// Pending tasks sorted by priority and due date
Filter pendingFilter = new Filter(
    new Criterion<bool>(
        tasks["is_completed"],
        ConditionalType.Equals,
        false
    )
);

Query query = new Query()
    .Select([
        tasks["task_id"],
        tasks["task_name"],
        tasks["priority"],
        tasks["due_date"]
    ])
    .From(tasks)
    .Where(pendingFilter)
    .OrderBy([
        new OrderClause(tasks["priority"], OrderClauseType.Descending), // High priority first
        new OrderClause(tasks["due_date"], OrderClauseType.Ascending)   // Soonest first
    ]);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Task Priority Queue:");
    Console.WriteLine("====================");

    foreach (Result row in results)
    {
        int taskId = row.GetFieldInteger("task_id");
        string taskName = row.GetFieldString("task_name");
        int priority = row.GetFieldInteger("priority");
        DateTime dueDate = row.GetFieldDateTime("due_date");

        string urgency = dueDate < DateTime.Now.AddDays(1) ? "[URGENT]" : "";

        Console.WriteLine($"[P{priority}] {taskName} {urgency}");
        Console.WriteLine($"      Due: {dueDate:g}");
    }
}

Best Practices

  1. Index sorted columns

    • Add indexes to columns used in ORDER BY
    • Dramatically improves query performance
    • Especially important for large tables

  2. Sort in the database, not in memory

    • Let the database handle sorting
    • More efficient than sorting in application code
    • Databases optimize sorting operations

  3. Consider query performance

    • Sorting large result sets can be expensive
    • Use WHERE clauses to reduce rows before sorting
    • Limit results when appropriate

  4. Order matters in multi-column sorts

    • Primary sort column comes first
    • Each additional column sorts within the previous
    • Test different orders for best results

  5. Be consistent with sort direction

    • Use consistent ASC/DESC patterns
    • Makes code more maintainable
    • Easier for users to understand results

  6. Handle NULLs explicitly

    • Don't rely on database-specific NULL sorting
    • Filter NULLs separately when needed
    • Document NULL handling behavior

  7. Use expressions sparingly

    • Sorting by expressions can prevent index usage
    • Consider pre-calculating values when possible
    • Test performance with large datasets

Next Steps