FROM [dbo].[DimProduct] A first shortcoming of T-SQL is introduced. When the database program uses the FORMAT() function, the result is that the datatype for the numeric WHERE [EnglishProductName] LIKE '%Road%' column is changed to character field, and the sorting stops working ORDER BY [List Price] DESC correctly. Just remove the FORMAT() syntax to fix the problem. It is widely recommended to perform the formatting in the reporting or data visualization software, rather than in SQL. SELECT [EnglishProductName], [Class] Just changing the Class and Color of course produces a completely different , Format([ListPrice], 'N0') as [List Price] list FROM [dbo].[DimProduct] WHERE Class = 'M' AND [EnglishProductName] LIKE '%Red%' ORDER BY [List Price] DESC -----a different resultset if retrieved if you change the criteria-- SELECT [EnglishProductName], [Class] , Format([ListPrice], 'N0') as [List Price] FROM [dbo].[DimProduct] WHERE Class = 'L' AND [EnglishProductName] LIKE '%Silver%' ORDER BY [List Price] DES USE [AdventureWorksDW2012]; What if you want to see the product line for the Road bikes? What if you want to just see the red road bikes? SELECT [EnglishProductName], [Class] What if the customer has been measured and sized for the 52 cm , ListPrice as [List Price] model? Can you show a list of products that they can further FROM [dbo].[DimProduct] examine? Can you retrieve a list of seats? Of bicycle helmets?
FROM [dbo].[DimProduct] WHERE [EnglishProductName] LIKE '%Road%' ORDER BY [List Price] DESC A first shortcoming of T-SQL is introduced. When the database program uses the FORMAT() function, the result is that the datatype for the numeric column is changed to character field, and the sorting stops working correctly. Just remove the FORMAT() syntax to fix the problem. It is widely recommended to perform the formatting in the reporting or data visualization software, rather than in SQL. SELECT [EnglishProductName], [Class] , Format([ListPrice], 'N0') as [List Price] FROM [dbo].[DimProduct] WHERE Class = 'M' AND [EnglishProductName] LIKE '%Red%' ORDER BY [List Price] DESC -----a different resultset if retrieved if you change the criteria-- SELECT [EnglishProductName], [Class] , Format([ListPrice], 'N0') as [List Price] FROM [dbo].[DimProduct] WHERE Class = 'L' AND [EnglishProductName] LIKE '%Silver%' ORDER BY [List Price] DES Just changing the Class and Color of course produces a completely different list USE [AdventureWorksDW2012]; SELECT [EnglishProductName], [Class] , ListPrice as [List Price] FROM [dbo].[DimProduct] What if you want to see the product line for the Road bikes? What if you want to just see the red road bikes? What if the customer has been measured and sized for the 52 cm model? Can you show a list of products that they can further examine? Can you retrieve a list of seats? Of bicycle helmets?
Featherman-TSQL-Primer-12-29-16.docx