/* 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