DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to sort on multiple conditions?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate a data-driven ORDER BY columns.

-- SQL order by case - SQL case order by - SQL conditional sort - dynamic sorting

USE AdventureWorks

GO

 

DECLARE  @SortBy          CHAR(10),

         @SortByDirection CHAR(1)

 

SET @SortBy = 'City'

SET @SortByDirection = 'A' -- or D for descending

 

SELECT   AddressLine1,

         City,

         State = p.Name,

         PostalCode

FROM     Person.Address a

         INNER JOIN Person.StateProvince p

           ON a.StateProvinceID = p.StateProvinceID

WHERE    AddressLine1 LIKE '%drive%'

ORDER BY CASE

           WHEN @SortBy = 'Postal'

                AND @SortByDirection = 'D' THEN PostalCode

         END DESC,

         CASE

           WHEN @SortBy = 'Postal'

                AND @SortByDirection = 'A' THEN PostalCode

         END,

         CASE

           WHEN @SortBy = 'State'

                AND @SortByDirection = 'D' THEN p.[Name]

         END DESC,

         CASE

           WHEN @SortBy = 'State'

                AND @SortByDirection = 'A' THEN p.[Name]

         END,

         CASE

           WHEN @SortBy = 'City'

                AND @SortByDirection = 'D' THEN City

         END DESC,

         CASE

           WHEN @SortBy = 'City'

                AND @SortByDirection = 'A' THEN City

         END

 

GO

 

/* Partial results

 

AddressLine1            City        State       PostalCode

42500 West Park Drive   Atlanta     Georgia     30308

4167 Whitehall Drive    Ballard     Washington  98107

4304 Dos Rios Drive     Ballard     Washington  98107

4930 Augustine Drive    Ballard     Washington  98107

5793 St. Helena Drive   Ballard     Washington  98107

630 Plymouth Drive      Ballard     Washington  98107

1727 The Trees Drive    Ballard     Washington  98107

*/

-----------

 

-----------

-- SELECT TOP 10 per group; CTE ORDER BY ROW_NUMBER PARTITION BY; SQL Server 2005 and on

-----------

;WITH CTE AS

(SELECT   TOP 1 WITH TIES PSC.Name AS SubCategory,

                          P.Name   AS ProductName

 FROM     Production.ProductSubcategory PSC

          INNER JOIN Production.Product P   -- alternate LEFT JOIN

            ON P.ProductSubcategoryID = PSC.ProductSubcategoryID

 ORDER BY ROW_NUMBER()

           OVER(PARTITION BY P.ProductSubcategoryID

           ORDER BY ProductID) / (10+1) )

 SELECT * FROM CTE

 ORDER BY Subcategory,

          ProductName;

------------

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE