Getting started with Microsoft SQL ServerOVER ClausePIVOT / UNPIVOTDatabase SnapshotsRetrieve information about the databaseThe STUFF FunctionFOR XML PATHCursorsJoinCommon Table ExpressionsMove and copy data around tablesDatesLimit Result SetRetrieve Information about your InstanceWith Ties Option VariablesJSON in Sql ServerWindow functionsPartitioningStored ProceduresGROUP BYGenerating a range of datesCOALESCESplit String function in Sql ServerINSERT INTOCREATE VIEWString FunctionsResource GovernorORDER BYWHILE loopSystem database - TempDbMigrationPrimary KeysMERGEFull-Text IndexingFOR JSONSELECT statementDBMAILIndexQueries with JSON dataStoring JSON in SQL tablesOPENJSONRanking FunctionsTriggerConverting data typesNULLsTransaction isolation levelsAdvanced optionsIF...ELSETRY/CATCHData TypesUser Defined Table TypesTable Valued ParametersIn-Memory OLTP (Hekaton)Temporal TablesInsertSequencesSCOPE_IDENTITY()ViewsUse of TEMP TableScheduled Task or JobIsolation levels and lockingSorting/ordering rowsPrivileges or PermissionsForeign KeysSQLCMDFile Groupcross applyBasic DDL Operations in MS SQL ServerComputed ColumnsUNIONSubqueriesLast Inserted IdentityCLUSTERED COLUMNSTOREParsenameInstalling SQL Server on WindowsAggregate FunctionsQuerying results by pageSchemasBackup and Restore DatabaseTransaction handlingNatively compiled modules (Hekaton)Database permissionsSpatial DataDynamic SQLPaginationQuery HintsModify JSON textRow-level securityDynamic data maskingExport data in txt file by using SQLCMDEncryptionManaging Azure SQL DatabaseCommon Language Runtime IntegrationDelimiting special characters and reserved wordsCASE StatementDBCCBULK ImportQuery StoreService brokerAnalyzing a QueryMicrosoft SQL Server Management Studio Shortcut KeysPermissions and SecurityPHANTOM readFilestreamDrop KeywordString Aggregate functions in SQL ServerSQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical FunctionsDynamic SQL PivotAlias Names in Sql Serverbcp (bulk copy program) Utility

ORDER BY

Other topics

Remarks:

The purpose of the ORDER BY clause is to sort the data returned by a query.

It's important to note that the order of rows returned by a query is undefined unless there is an ORDER BY clause.

See MSDN documentation for full details of the ORDER BY clause: https://msdn.microsoft.com/en-us/library/ms188385.aspx

Simple ORDER BY clause

Using the Employees Table, below is an example to return the Id, FName and LName columns in (ascending) LName order:

SELECT Id, FName, LName FROM Employees
ORDER BY LName

Returns:

IdFNameLName
2JohnJohnson
1JamesSmith
4JohnathonSmith
3MichaelWilliams

To sort in descending order add the DESC keyword after the field parameter, e.g. the same query in LName descending order is:

SELECT Id, FName, LName FROM Employees
ORDER BY LName DESC

ORDER BY multiple fields

Multiple fields can be specified for the ORDER BY clause, in either ASCending or DESCending order.

For example, using the http://stackoverflow.com/documentation/sql/280/example-databases/1207/item-sales-table#t=201607211314066434211 table, we can return a query that sorts by SaleDate in ascending order, and Quantity in descending order.

SELECT ItemId, SaleDate, Quantity
FROM [Item Sales]
ORDER BY SaleDate ASC, Quantity DESC

Note that the ASC keyword is optional, and results are sorted in ascending order of a given field by default.

ORDER BY with complex logic

If we want to order the data differently for per group, we can add a CASE syntax to the ORDER BY. In this example, we want to order employees from Department 1 by last name and employees from Department 2 by salary.

IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHireDate
1JamesSmith1234567890NULL1100001-01-2002
2JohnJohnson24681012141140023-03-2005
3MichaelWilliams13579111311260012-05-2009
4JohnathonSmith12121212122150024-07-2016
5SamSaxon13721413122240025-03-2015
The following query will provide the required results:
SELECT Id, FName, LName, Salary FROM Employees
ORDER BY Case When DepartmentId = 1 then LName else Salary end

Custom Ordering

If you want to order by a column using something other than alphabetical/numeric ordering, you can use case to specify the order you want.

order by Group returns:

GroupCount
Not Retired6
Retired4
Total10

order by case group when 'Total' then 1 when 'Retired' then 2 else 3 end returns:

GroupCount
Total10
Retired4
Not Retired6

Contributors

Topic Id: 4149

Example Ids: 14506,14507,17656,26830

This site is not affiliated with any of the contributors.