John Sheehan : Blog

Slightly more dynamic ORDER BY in SQL Server 2005

Dynamically ordering SELECT statements in T-SQL has been an issue I’ve run into a lot. The options have traditionally been use dynamic SQL (eww) or a CASE statement in the ORDER BY. CASE kind of works, but its limited (only one column, no sort directions, datatype issues, etc).

I needed a way to dynamically sort the results of a stored procedure by multiple columns with different combinations of sort directions (e.g. Customer.LastName DESC, Customer.FirstName). I’m fortunate that in my situation there’s only six possible sort combinations based on CustomerTypeID. If you’re looking for a way to pass a string parameter to your stored procedure and sort by that; as far as I can tell you’re still stuck with the existing workarounds. Alright, let’s get into the example.

Create the following tables and test data. Remember, this example only works with SQL Server 2005.

CREATE TABLE Customer
(
    CustomerID int identity(1,1),
    FirstName nvarchar(50),
    LastName nvarchar(50),
    CustomerTypeID int
)

CREATE TABLE CustomerType
(
    CustomerTypeID int identity(1,1),
    CustomerType nvarchar(50)
)

--Insert test data

INSERT INTO CustomerType (CustomerType) VALUES('Type1') -- ID=1
INSERT INTO CustomerType (CustomerType) VALUES('Type2') -- ID=2
INSERT INTO CustomerType (CustomerType) VALUES('Type3') -- ID=3

INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('John', 'Doe', 1)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Jane', 'Doe', 1)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Steve', 'Jobs', 2)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Steve', 'Wozniak', 2)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Linus', 'Torvalds', 2)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Bill', 'Gates', 3)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Michael', 'Dell', 3)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Steve', 'Ballmer', 3)
INSERT INTO Customer (FirstName, LastName, CustomerTypeID) VALUES ('Ray', 'Ozzie', 3)

Let’s say we need a procedure to return customers of a certain type. The proceedure needs to sort result sets with Customers of TypeID=1 by FirstName then LastName. Customers of TypeID=2 by LastName then FirstName and lastly Customers of TypeID=3 as LastName DESC then FirstName.

This is where SQL Server 2005 comes into play. SQL 2005 has introduced some new ranking functions like RANK(), ROW_NUMBER(), etc. We’re going to use RANK() here. Because RANK() accepts multiple ORDER BY statements, we can use the return value to order our result set.

First, let’s see the results of using the RANK() function in our stored procedure.

CREATE PROCEDURE CustomerGetWithRanks
AS
BEGIN
    SELECT
        CustomerID,
        FirstName,
        LastName,
        CustomerTypeID,
        RANK() OVER (ORDER BY FirstName, LastName) AS Type1SortRank,
        RANK() OVER (ORDER BY LastName, FirstName) AS Type2SortRank,
        RANK() OVER (ORDER BY LastName DESC, FirstName DESC) AS Type3SortRank
    FROM
        Customer
END

Results:

CustomerID FirstName LastName  CustomerTypeID Type1SortRank Type2SortRank Type3SortRank
---------- --------- --------- -------------- ------------- ------------- -------------
7          Steve     Wozniak   2              9             9             1
8          Linus     Torvalds  2              4             8             2
12         Ray       Ozzie     3              6             7             3
6          Steve     Jobs      2              8             6             4
9          Bill      Gates     3              1             5             5
4          John      Doe       1              3             4             6
5          Jane      Doe       1              2             3             7
10         Michael   Dell      3              5             2             8
11         Steve     Ballmer   3              7             1             9

You can see how each set of sort criteria would sort all of our test records. I only need to retrieve one type at a time however, so I’m going to combine this method with the previously limited CASE method.

The last piece of the puzzle is adding the logic to determine which sort order to use based on CustomerTypeID. We’re also going to narrow down our result set to only return a specific type of Customer and hide the RANK() output.

CREATE PROCEDURE CustomerGetFinal
    @CustomerTypeID int
AS
BEGIN
    SELECT
        CustomerID,
        FirstName,
        LastName,
        CustomerTypeID
    FROM
        Customer
    WHERE
        CustomerTypeID = @CustomerTypeID
    ORDER BY
        CASE
            WHEN @CustomerTypeID = 1 THEN (RANK() OVER (ORDER BY FirstName, LastName))
            WHEN @CustomerTypeID = 2 THEN (RANK() OVER (ORDER BY LastName, FirstName))
            WHEN @CustomerTypeID = 3 THEN (RANK() OVER (ORDER BY LastName DESC, FirstName DESC))
        END
END

(NOTE: If you want to output the RANK() result and order by the same value, you have to specify the whole line twice as SQL server doesn’t appear to be accepting dynamically created columns as ORDER BY parameters). Here are the results:

EXEC CustomerGetFinal 1

CustomerID FirstName LastName CustomerTypeID
---------- --------- -------- --------------
5          Jane      Doe      1
4          John      Doe      1

EXEC CustomerGetFinal 2

CustomerID FirstName LastName CustomerTypeID
---------- --------- -------- --------------
6          Steve     Jobs     2
8          Linus     Torvalds 2
7          Steve     Wozniak  2

EXEC CustomerGetFinal 3

CustomerID FirstName LastName CustomerTypeID
---------- --------- -------- --------------
12         Ray       Ozzie    3
9          Bill      Gates    3
10         Michael   Dell     3
11         Steve     Ballmer  3