Which one is better, why ?

Posted by Devanand under Sql Server on 5/4/2010 | Views : 1439 | Status : [Member] | Replies : 3
hi dude

I have been asked one question like :

Which one is better to avoide the duplicate of data for the following scenario ?

table name : tbl_erpsale

query: 1
select distinct SaleDate from tbl_erpsale

query: 2
select SaleDate from tbl_erpsale group by SaleDate

In my above scenario, which one is best. My seniors saying like "group by" is better than the "distinct". But, they don't know the exact reason. But they using this way only

could anybody explain me the reason ? please ?

yours
Devanand




Responses

Posted by: Diya0076 on: 5/4/2010 [Member] Starter

Up
0
Down
To avoid duplicate data distinct is used

and group by is used with aggregate functions..

just search on web the difference b|W this both .. and also the examples to understand their meaning more perfectly



Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Er.deepakdalal on: 5/4/2010 [Member] Starter

Up
0
Down
Hi Devanand

I think in this situation distinct is faster then the Group By and the difference between them is :-

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:
SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:
SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:
SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nishithraj on: 5/5/2010 [Member] Bronze

Up
0
Down
If there is only one table and one group, it's better to use distinct instead of group by.

Group by is specially meant for grouping data as the name mentioned. So if there is no grouping required, distinct provides better perfomance than group by. If it's vice versa go for grouping.

Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response