Pages

3 May 2015

SQL- Scenario Based Questions PART- 3

Q. How to get distinct rows from SQL table without using DISTINCT keyword .

CREATE TABLE ProductDetail
( ProductID Int,
  ProductName Varchar(30),
  Price int)

  INSERT INTO ProductDetail Values(101,'Dove',55)
  INSERT INTO ProductDetail Values(101,'Dove',55)
  INSERT INTO ProductDetail Values(102,'Hamam',30)
  INSERT INTO ProductDetail Values(103,'Cinthol',35)
  INSERT INTO ProductDetail Values(103,'Cinthol',35)

ProductDetail
To achieve distinct records from a table, we can do by following two methods:

Solution#1: By using GROUP BY clause. By selecting all columns and then grouping by all columns gives distinct records.

  SELECT ProductID,ProductName,Price
  FROM ProductDetail
  GROUP BY ProductID,ProductName,Price

Solution#1_ProductDetail
Solution#2: By using UNION . We know that UNION always gives distinct record from a table. So By doing UNION between the same table will give unique records.

  SELECT ProductID,ProductName,Price
  FROM ProductDetail
  UNION
  SELECT ProductID,ProductName,Price
  FROM ProductDetail

Solution#2_ProductDetail
For other SQL Scenario Based Questions, please visit below link
http://itjunction4all.blogspot.com/2014/04/sql-scenario-based-questions-part-2.html