distinct versus group by
-
Hi...
I would like to ask this question
which one is faster between query "select using group by" or "select using distinct" ? or using both ("select distinct bla bla...group by...)
My query should be like this :
select distinct fieldA,fieldB
from tblA
or
select fieldA,fieldB
from tblA
group by fieldA,fieldB ?
thanks...
-
DISTINCT is a simplest case of GROUP BY.
_____________
Code for TallyGenerator -
Anecdotally, distinct is often faster, although I do not know why--I much prefer group by, myself, as I prefer to have the demographics as well
-
I prefer DISTINCT, if the only thing I need to do is remove duplicate rows. It looks cleaner in the code, you don't have to check select list and group by list to find out whether all columns are included in the GROUP BY. On first glance, you can see what it does.
However, if you later need to add some statistics (COUNT and such), you have to rewrite part of the query if it uses DISTINCT.
I've never observed any meaningful difference in performance, though I haven't tested it specifically on large amounts of data. (BTW, query plan is the same for both queries - at least for a simple query of the type you posted).
Decide for yourself... I am for the DISTINCT.
-
If you adding another field to query with distinct without proper consideration you are probably changing grooping of data accidentally.
GROOP BY will stop you from it asking add the field to groop by or use some aggregate function for it.
It's useful sometimes to have reminder that you are trying to change logic of query.
_____________
Code for TallyGenerator -
thanks guys...for all your inputs...
I think I prefer "distinct" to remove duplicate records...but when I need to count or do some aggregate functions I will use group by...Besides, group by is also useful to see the logic of the query
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply