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 use DISTINCT in SELECT?

Execute the following Microsoft SQL Server T-SQL database scripts in Management Studio Query Editor to demonstrate the use of the DISTINCT keyword. DISTINCT is a very expensive operation.

-- Using DISTINCT in COUNT - QUICK SYNTAX

SELECT TotalRows=COUNT(*), ProductColors=COUNT(DISTINCT Color),

       ProductSizes=COUNT(DISTINCT Size)

FROM AdventureWorks2008.Production.Product

/* TotalRows      ProductColors     ProductSizes

      504         9                 18                */
------------

-- Applying SQL Server DISTINCT to eliminate duplicates in table or result set

SELECT DISTINCT Size, Color

INTO ProdSizeColorCombination

FROM AdventureWorks2008.Production.Product

-- (68 row(s) affected)

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

-- SQL DISTINCT usage for colors - SQL Server distinct - Select distinct
SELECT DISTINCT ProductColors = COALESCE(Color, 'N/A')
FROM AdventureWorks2008.Production.Product
ORDER BY ProductColors
/*
ProductColors
Black
Blue
Grey
Multi
N/A
Red
Silver
Silver/Black
White
Yellow */
------------
 
-- SQL DISTINCT for order years - SQL Server select distinct on one column
SELECT DISTINCT [Year]=YEAR(OrderDate)
FROM AdventureWorks2008.Sales.SalesOrderHeader
ORDER BY [Year]
GO
/*
Year
2001
2002
2003
2004 */
------------

-- T-SQL COUNT DISTINCT usage - sql count distinct / unique rows
SELECT

      AllProducts=                        COUNT(*),

      ProductsWithSubcategories =         COUNT(ProductSubcategoryID),

      UniqueSubcategories =               COUNT(DISTINCT ProductSubcategoryID),

      ProductsWithColor =                 COUNT(ALL Color),

      UniqueColors =                      COUNT(DISTINCT Color)

FROM AdventureWorks2008.Production.Product

/*

AllProducts ProductsWithSub. UniqueSubcategories ProductsWithColor UniqueColors

504         295               37                  256                  9 */

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

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

-- T-SQL SELECT DISTINCT complex usage - create table with distinct ProductID-s

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

SELECT RowID=ROW_NUMBER() OVER (ORDER BY ProductID),          -- SELECT FROM SELECT

       ProductID

INTO ProdInv                                                  -- Table create

FROM (SELECT DISTINCT ProductID

      FROM AdventureWorks2008.Production.ProductInventory ) x  -- Derived table

-- (425 row(s) affected)

SELECT TOP (7) * FROM ProdInv

/* RowID    ProductID

      1           1

      2           2

      3           3

      4           4

      5           316

      6           317

      7           318   */

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

 

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