This question is asked many times to me. What is difference between DISTINCT and GROUP BY?
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
Reference : Pinal Dave (https://blog.sqlauthority.com)
97 Comments. Leave new
Hi Pinal,
can you tell me why count of distinct id’s [id’s are 0]=1?
Hi….
I am facing one problem that when I fire above query it will give me multiple records for single ItemNo but I wanna only unique records. What I need to do for this?
Select distinct tg.ItemNo,i.TaxIncExc,case WHEN g.TaxFrom=’Inside Maharashtra’
THEN i.TaxInsideMaha Else i.TaxOutsideMaha END as TaxPercent,i.ItemName,
((tg.Qty*i.mrp)*(ISNULL(i.Excise,0)))/100 as Excise,tg.Price,’0′ as TAxAmt,tp.ItemQty,
tg.Qty as AcceptedQty,tp.BalanceQty,” as ExpiryDate,tg.RejectedQty,
(tg.Qty*i.mrp) as TotalPrice from trn_grn tg inner join mst_item i
on i.itemno=tg.itemno inner join mst_grn g on g.grnno=tg.grnno inner join mst_employee e
on e.empno=tg.inspector inner join mst_po p inner join Trn_PO tp on tp.PONo=p.PONo
on p.pono=g.pono where g.potype=’Normal_PO’and g.pono=27
Hello Pinal, I never opine but I constantly read your posts. Your contributions to the SQLers are priceless, I just wanted to take the time to say thank you!
Hi colleagues, its fantastic piece of writing regarding educationand fully explained, keep it
up all the time.
your posts always prove to be of a high value and quality for readers.
Thanks Praveen
Pinal Dave, thank you for your postings; you have been extremely helpful. Whenever I have a question, I usually Type “Pinal Dave” followed by the question, You are always Spot ON. Thanks again for sharing the knowledge.
So nice of you GG. Thanks.
Can we use Distinct with Group By in sub query ?
select
o.empname,prname,qty,sp,total
from
order as o
join
orderdetails as od on
o.orderid = od.orderid
where
o.empname = ‘Jhon’;
//change the uper and lower case accordingly