Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Different ways to find DISTINCT values

Nov 16 2012 12:00AM by Madhivanan   

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.

Tags: 


Madhivanan
3 · 40% · 12862
12
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

6  Comments  

  • If you ask me, you should use Paul White's super fast DISTINCT method.

    http://www.sqlservercentral.com/Forums/Topic873124-338-5.aspx#bm1013407

    commented on Nov 16 2012 12:12AM
    Dwain Camps
    1477 · 0% · 14
  • Thanks Dwain camps for that link. That method is very fast provided that the column has an index. Otherwise it is 100 times slower than other methods shown here.

    commented on Nov 16 2012 2:20AM
    Madhivanan
    3 · 40% · 12862
  • it's intersting! ,ths, madhivanan

    commented on Nov 17 2012 7:43AM
    goodasong
    1073 · 0% · 24
  • nice article - thanks

    commented on Nov 19 2012 3:39AM
    knookie
    1591 · 0% · 12
  • HI,

    Interesting post - would u happen to know what the relative performance is on the difference methods, what situations call for one approach in lieu of another...

    commented on Nov 19 2012 10:27AM
    lorrin.ferdinand
    3015 · 0% · 2
  • This article came in the nick of time. Needed to create a view using distinct data. But you cannot put an index on a view that uses DISTINCT or a CTE. I ended up using the GROUP BY method, but you must use COUNT_BIG for some reason.

    Update: Turns out this didn't work as well as I thought. Problem is, every once in a while, the name is a little different, so (for example) I'll have 2 rows for item# 1: 1, 'shirt' and 1,'shert'. The method using row_number() would be ideal, but then I won't be able to index the view. Yes, I could make a table but the table the view comes from grows each quarter, which is the whole point of a view, no? Any suggestions?

    commented on Nov 20 2012 9:22AM
    Amy
    493 · 0% · 77

Your Comment


Sign Up or Login to post a comment.

"Different ways to find DISTINCT values" rated 5 out of 5 by 12 readers
Different ways to find DISTINCT values , 5.0 out of 5 based on 12 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]