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
@shwaria
Remove comma (,) at the end of second line, to reduce confusion, you can use different column name.
select CurrentOperator, COUNT(*) Count_CurrentOperator from EZBusDB.dbo.Buses
GROUP BY CurrentOperator
~IM
Hi You need to Remove (,) from the end of your statement
I read your posts for quite a long time and should tell that your posts always prove to be of a high value and quality for readers.
hi thanks so much for between dif but when and why used this arti……..
Hye!
I’m trying a simple query with DISTINCT keyword as:
SELECT DISTINCT SecCode FROM Sections ORDER BY SecCode
in SQL Server 2005, but it is not giving me any result.
Can anyone help me?
Thanks!
Dear
Could u plz suggest me a technique where i can implement transactional replication technique with ONE – MANY Tables relationships……….. i want to replicate the different remote databases with same name n having same articles and structures in centralized single database with same single articles………………..
select DENSE_RANK() Over (Order by col1, col2 ) as Ranky, col1, col2 from table where…
group By Col1, col2 — gives distinct
Thanks Pinal,
These posts are very useful.
I found them after attending your presentation in teched delhi.
i try to get distinct values for name and get all other fields with out where condition
ex. table
id name level department companyid
1 ram 1 1 1
2 ram 2 2 2
3 kumar 3 3 3
4 Raja 4 4 4
i expect value is
1 ram 1 1 1
3 kumar 3 3 3
4 Raja 4 4 4
(or)
1 ram 1 1 1
2 ram 2 2 2
4 Raja 4 4 4
try like this..
DECLARE @TEMPTABLE TABLE(ID BIGINT ,NAME VARCHAR(8),EmpID INT,departmentID INT,companyid INT)
INSERT INTO @TEMPTABLE
VALUES(1,’ram’,1,1,1),(2,’ram’,2,2,2),(3,’kumar’,3,3,3),(4,’Raja’,4,4,4)
SELECT ID,Name,EmpID,departmentID,companyid FROM
(
SELECT ID,Name,EmpID,departmentID,companyid,Row_number() over (partition by name order by EmpID,departmentID,companyid) AS RowID
FROM @TEMPTABLE
)
AS A WHERE RowID=1
ORDER BY ID
Thanks Pinal.
Very useful posts.
Hello I am trying to make a selection in 3 different tables using distinct, but I keep getting duplicate values, my tables are Alumni, History, and Groups, I get the id and name of students enrolled in the group 230, my query is
SELECT DISTINCT H. Go, A. LastName, A. Name FROM History H, Alumni A, Groups I WHERE Group = 230 AND H. H.id = I.id AND ORDER BY I.key = A.key H.id ” )
appear two students with the same value:
9876 M John
9876 M John
I hope you can help me as no way out, and I’m starting to use SQL.
@Lizbeth Morales
The query didn’t seem to come through correctly.
Please repost it, along with the COLUMNs in each TABLE used. It is hard to determine what is going on without them.
Thanks
useful info
select name,count(*) as No_Of_Visit,sum(amount)from customer group by name
How about:
SELECT code, Month(d_date) as d_month, SUM(qty) as quantity
FROM t_test
GROUP BY code, d_month
or should I group by code, Month(d_date)?
Hi,
Yes, you should group by code, Month(d_date) to get perfect output otherwise you would get multiple records for each set of code and Month(d_date).
Regards,
Pinal Dave
Hi Pinal,
Is there a way to find the first non blank distinct data from query…
e.g.
Name Comment
————————————–
ABC
Ajay JKL
Vikas XYZ
i want result as below…. is there a way to get result from query ?
Name Comment
————————————–
Ajay ABC
@Jignesh
I do not understand what you want. I have tried something though:
WITH
Data(Name, Comment)
AS
(
SELECT NULL, ‘ABC’ UNION ALL
SELECT ‘Ajay’, ‘JKL’ UNION ALL
SELECT ‘Vikas’, ‘XYZ’
)
SELECT
(SELECT TOP(1) Name FROM Data WHERE Name IS NOT NULL ORDER BY Name ) Name,
(SELECT TOP(1) Comment FROM Data WHERE Comment IS NOT NULL ORDER BY Comment ) Comment;
Thanks.Brian. Even without format you understood properly !
Now i have second question is it possible to get COUNT IN nested distinct via SQL query from a table.
for example :
Enquiry No , Part No
—————————————
E1 P1
E1 P1
E2 P2
E2 P1
E3 P2
E3 P3
EXPECTED RESULT :
——————————————-
Enquiry NO DISTINCT_PART_COUNT
—————————————
E1 1
E2 2
E3 2
Any help is highly appreciated…
select [Enquiry No] , count(distinct [Part No]) as DISTINCT_PART_COUNT from your_table
group by [Enquiry No]
Hi Sally,
You can use ROW_NUMBER keyword to achieve expected output.
DECLARE @tmp TABLE(SrNo INT)
INSERT INTO @tmp VALUES(3)
INSERT INTO @tmp VALUES(3)
INSERT INTO @tmp VALUES(4)
INSERT INTO @tmp VALUES(5)
INSERT INTO @tmp VALUES(5)
INSERT INTO @tmp VALUES(5)
INSERT INTO @tmp VALUES(9)
INSERT INTO @tmp VALUES(8)
INSERT INTO @tmp VALUES(9)
INSERT INTO @tmp VALUES(9)
INSERT INTO @tmp VALUES(9)
INSERT INTO @tmp VALUES(9)
SELECT DISTINCT *,
COUNT(*) OVER(PARTITION BY SrNo) AS Total
FROM @tmp
This will run for SQL 2005 and above only.
Thanks,
Tejas
SQLYoga.com
CREATE table tmp (SrNo INT)
INSERT INTO tmp VALUES(3)
INSERT INTO tmp VALUES(3)
INSERT INTO tmp VALUES(4)
INSERT INTO tmp VALUES(5)
INSERT INTO tmp VALUES(5)
INSERT INTO tmp VALUES(5)
INSERT INTO tmp VALUES(9)
INSERT INTO tmp VALUES(8)
INSERT INTO tmp VALUES(9)
INSERT INTO tmp VALUES(9)
INSERT INTO tmp VALUES(9)
INSERT INTO tmp VALUES(9)
select top 1 srno
from tmp
group by srno
order by count(srno)
desc
I’ll use DISTINCT as it is the true meaning of what I want to achieve… I had just heard in the past that GROUP BY was a lot more performant, but like you say it should execute the same query plan – and I can always check that too!
So thanks, I am now not as “anti-DISTINCT” as I was before I read your post. ;-)
Cheers.