SQL fastest 'GROUP BY' script

Question

Is there any difference in how I edit the GROUP BY command?

my code:

SELECT Number, Id 
    FROM Table
    WHERE(....)
    GROUP BY Id, Number

is it faster if i edit it like this:

 SELECT Number, Id 
    FROM Table
    WHERE(....)
    GROUP BY Number , Id

score 3 · Answer 1 · answered Jul 15 '13 at 09:20

3

it's better to use DISTINCT if you don't want to aggregate data. Otherwise, there is no difference between the two queries you provided, it'll produce the same query plan

answered Jul 15 '13 at 09:20

jazzytomato

6,994
2
31
44

So there order of the columns in the select command has nothing to do with the order of the columns in the Group by command? – nionios Jul 15 '13 at 09:25
No it doesn't, but it makes sense : try to do a *GROUP BY* yourself with little data, a paper and a pencil :) – jazzytomato Jul 15 '13 at 09:31

Devart · Accepted Answer · 2013-07-15T09:34:06.087

2

This examples are equal.

DDL:

CREATE TABLE dbo.[WorkOut]
(
    [WorkOutID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [TimeSheetDate] [datetime] NOT NULL,
    [DateOut] [datetime] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [IsMainWorkPlace] [bit] NOT NULL,
    [DepartmentUID] [uniqueidentifier] NOT NULL,
    [WorkPlaceUID] [uniqueidentifier] NULL,
    [TeamUID] [uniqueidentifier] NULL,
    [WorkShiftCD] [nvarchar](10) NULL,
    [WorkHours] [real] NULL,
    [AbsenceCode] [varchar](25) NULL,
    [PaymentType] [char](2) NULL,
    [CategoryID] [int] NULL
)

Query:

SELECT wo.WorkOutID, wo.TimeSheetDate 
FROM dbo.WorkOut wo
GROUP BY wo.WorkOutID, wo.TimeSheetDate

SELECT DISTINCT wo.WorkOutID, wo.TimeSheetDate 
FROM dbo.WorkOut wo

SELECT wo.DateOut, wo.EmployeeID
FROM dbo.WorkOut wo
GROUP BY wo.DateOut, wo.EmployeeID

SELECT DISTINCT wo.DateOut, wo.EmployeeID 
FROM dbo.WorkOut wo

Execution plan:

edited Jul 15 '13 at 09:34

answered Jul 15 '13 at 09:24

Devart

119,203
23
166
186

Be careful as your examples states that it is all the same; but you should not use group by to eliminate duplicates, this is not correct and it might be result in different execution plans when it comes to real tables (because of the usage of indexes) – jazzytomato Jul 15 '13 at 09:27
1

For what the minus? Please comment. – Devart Jul 15 '13 at 09:35
@Thomas Haratyk, add execution plan for the real table. – Devart Jul 15 '13 at 09:36
Nice to have a real example but still, it doesn't prove your point (I can't prove mine either :-) ). I know I read it somewhere... However I think it is far more readable to use *distinct* – jazzytomato Jul 15 '13 at 09:44
see http://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql (this is for MySql but it doesn't mean you should take bad habits) – jazzytomato Jul 15 '13 at 10:14

SQL fastest 'GROUP BY' script

2 Answers2