Finding distinct values is often needed in such cases like finding distinct items that were sold last month, etc. You can very well use DISTINCT keyword to do this. However there are some other ways too to find distinct values
Consider the following set of data
Create table #sales_details
(
sales_id int identity(1,1),
item_id int not null,
qty int not null,
unit_price decimal(12,2) not null,
sales_date datetime not null
)
insert into #sales_details (item_id,qty,unit_price,sales_date)
select 1001,5,200,'2012-09-03 11:16:28' union all
select 1001,2,200,'2012-09-04 19:22:11' union all
select 1002,15,1300,'2012-09-06 14:26:40' union all
select 1003,8,78,'2012-09-19 15:11:19' union all
select 1001,6,200,'2012-09-22 16:36:11' union all
select 1004,22,1000,'2012-09-23 16:51:34' union all
select 1004,11,1000,'2012-09-23 17:29:38' union all
select 1002,29,1300,'2012-09-23 18:20:10' union all
select 1002,6,1300,'2012-09-26 19:40:41' union all
select 1002,33,1300,'2012-09-30 20:26:29'
Assume that you want to find out distinct item_id from the above table. You can use many methods. Some of them are listed below
Method 1 : Use DISTINCT keyword
select distinct item_id from #sales_details
Method 2 : Use GROUP BY Clause select item_id from #sales_details
Group by item_id
Method 3 : UNION the same table select item_id from #sales_details
UNION
select item_id from #sales_details
Method 4 : UNION the same table with not selecting any rows from secondly sepecified table select item_id from #sales_details
UNION
select item_id from #sales_details where 1=0
Method 5 : UNION the table with Empty result select item_id from #sales_details
UNION
select 0 where 1=0
Method 6 : Use Row_number() function select item_id from
(
select row_number() over (partition by item_id order by item_id) as sno,* from #sales_details
) as t
where sno=1
All the queries return the following data
item_id
-----------
1001
1002
1003
1004
Note :
Method 3 will be ineffecient as the same resultset is used twice. Methods 4 and 5 are very tricky. They show how you can use UNION to find DISTINCT values effectively. These are useful only if you want to find out DISTINCT values for single or set of columns. However if you are in need of finding DISTINCT values like COUNT(DISTINCT item_id) with some GROUP BY clauses, these may not be useful.
If you ask me, I would prefer using Method 1 for finding DISTINCT values in cases like above, because it is simple and straight forward.