DISTINCT – SQL Server Syntax Example: DISTINCT – T-SQL Example

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

DISTINCT – SQL Server Syntax Example: DISTINCT – T-SQL Example

Purpose: – Illustrates the SQL Server syntax for the DISTINCT.

SYNTAX:

SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
[ INTO new_table ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE search_condition AND search_condition ]
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]

PURPOSE:
DISTINCT allows you to ORDER BY a column in DISTINCTending order.

PURPOSE: DISTINCT allows you to only select rows that have distinct value for column(s). DISTINCT can only occur once and must precede all column names.

Code Sample for DISTINCT:

 T-SQL | 
 copy code |
?
 
 /* DISTINCT example from http://idealprogrammer.com 
 PURPOSE: DISTINCT allows you to only select rows that have distinct value 
          for column(s).  DISTINCT can only occur once and must precede all
          column names.
 
 SYNTAX: SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column1 AS displayname1, displayname2 = column_name2, column_name3 displayname3  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE search_condition AND search_condition ] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          int,
     firstname   varchar (20),
     lastname    varchar (20),
     statecode   varchar (2),
     alive       bit,
     height      int )
 GO
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 72)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'John', 'Lennon', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Steve', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'MA', 0, 65)
 GO
 
--Show entire table
 SELECT statecode FROM people;
 GO
 
 
-- 1. Example shows selecting distinct values
 SELECT DISTINCT statecode  
  FROM people  
 GO
 
 -- 2. Example shows using distinct with count
 SELECT COUNT(DISTINCT statecode)  
  FROM people  
 GO
 
 
 DROP TABLE people;
 
 GO
 
 
 
 
 
 
 
 
 

Sample Output for DISTINCT Example

Related posts:

1. DESC – SQL Server Syntax Example: DESC – T-SQL Example DESC - SQL Server Syntax Example: DESC - T-SQL Example...
2. Column Alias – SQL Server Syntax Example: Column Alias – T-SQL Example Column Alias - SQL Server Syntax Example: Column Alias -...
3. Column AS Alias SQL Server Syntax Example: Column AS T-SQL Example SELECT AS SQL Server Syntax Example: SELECT AS T-SQL Example...
4. BETWEEN AND – SQL Server Syntax Example: BETWEEN AND – T-SQL Example BETWEEN AND - SQL Server Syntax Example: BETWEEN AND -...
5. SELECT CASE – SQL Server Syntax Example: SELECT CASE – T-SQL Example SELECT CASE - SQL Server Syntax Example: SELECT CASE -...

Related posts brought to you by Yet Another Related Posts Plugin.