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