What's better, GROUP BY or DISTINCT
Hello everybody,
If I want to get distinct values, I can use either DISTINCT or GROUP BY - the usage can vary of course, but this two queries:
SELECT DISTINCT city FROM clients
and
SELECT city FROM clients GROUP BY city
the resultset will be identical. I have heard or read somewhere that using DISTINCT should be avoided if possible - I don't insist on this statement but since it's sitting somewhere at the back of my head, I'm just wondering, is it really bad to use DISTINCT in this case (the size of the table can be rather big), or using GROUP BY is better. And if yes, what's the advantage of one over another and vice versa.
Thanks you,
Yurich
If I want to get distinct values, I can use either DISTINCT or GROUP BY - the usage can vary of course, but this two queries:
SELECT DISTINCT city FROM clients
and
SELECT city FROM clients GROUP BY city
the resultset will be identical. I have heard or read somewhere that using DISTINCT should be avoided if possible - I don't insist on this statement but since it's sitting somewhere at the back of my head, I'm just wondering, is it really bad to use DISTINCT in this case (the size of the table can be rather big), or using GROUP BY is better. And if yes, what's the advantage of one over another and vice versa.
Thanks you,
Yurich
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<That would be possible if I have duplicates in my table, right?>>
yes.
yes.
ASKER
Thanks again,
"SQL 2000 DISTINCT operator is physically impemented as GROUP BY" - that's my answer ;)
Appreciate your help,
Yurich
"SQL 2000 DISTINCT operator is physically impemented as GROUP BY" - that's my answer ;)
Appreciate your help,
Yurich
Yurich,
> > "...make sure you limit your columns in the SELECT clause to those columns that produce a unique row"
> That would be possible if I have duplicates in my table, right?
That depends. In the example I gave, if you decided to go with SELECT DISTINCT, I would definitely leave
out a column like OrderID or OrderAmount or OrderDate: if the customer had more than one order in
the period, there will be multiple values in that column for the same customer, and the SELECT DISTINCT
would return all the records, because the whole record defines the uniqueness.
Patrick
> > "...make sure you limit your columns in the SELECT clause to those columns that produce a unique row"
> That would be possible if I have duplicates in my table, right?
That depends. In the example I gave, if you decided to go with SELECT DISTINCT, I would definitely leave
out a column like OrderID or OrderAmount or OrderDate: if the customer had more than one order in
the period, there will be multiple values in that column for the same customer, and the SELECT DISTINCT
would return all the records, because the whole record defines the uniqueness.
Patrick
ASKER
"...make sure you limit your columns in the SELECT clause to those columns that produce a unique row"
That would be possible if I have duplicates in my table, right?
Regs,
Yurich