Do aggregate functions work differently when assigned to a variable?

>> Does query1 work differently than query2 in sql server 2012?

Why do you think they work differently? They are identical, except for the fact that query 2 assigns the result to a variable.

>> Does query2 just gets the top 1 row?

No, like the first query it returns the MIN (ie minimum) value in COL1.

>> GROUP BY COL1

The GROUP is redundant. Grouping is only needed when mixing aggregates and non-aggregate columns. Since the SELECT only includes an aggregate. it is not necessary.

Both queries will throw an error as you have a GROUP BY without referencing that column in the SELECT clause.

Aside from that, if you remove the GROUP BY clause..
Query one returns the minimum value of Col1 as a one column set.  btw you need to give it a name.

Query two would store that same value into variable @var, and then since I don't see any other code would eventually exit the SP and do nothing with it.

Edit:

>>> Both queries will throw an error

Hm... it doesn't in 2008.  Just ran a quick test and from what I can tell, the 1st query returns all distinct values, and the 2nd populates the variable with what seems like the last value. Though without an ORDER BY I would guess the exact behavior is not guaranteed.

(That said, since the column is already included in an aggregate, not sure I see the purpose of the GROUP BY)

Hi,

Both queries will work perfectly fine in SQL SERVER 2012. Both Queries will give you different output.

Query 1 -will give you DISTINCT values present in col1.

Query 2 - will give you ANY value (assigned to the variable declared) from the DISTINCT values present in col1. Here you will get only 1 value that is also in the variable since variable can take one 1 value only.

Few samples to make things more clear. Table Creation-

CREATE TABLE Queryes
(
	ID INT
)
GO

INSERT INTO Queryes VALUES (1),(1),(2),(2),(3),(4)
GO

Select allOpen in new window

Query1

SELECT MIN(Id)
FROM Queryes
GROUP BY Id

Select allOpen in new window

Output - Note that we have 6 rows in the table but the queries will return 4 values

/*------------------------
SELECT Id
FROM Queryes
GROUP BY Id
------------------------*/
Id
-----------
1
2
3
4

(4 row(s) affected)

Select allOpen in new window

Query 2

DECLARE @ID AS INT
SELECT @ID = MIN(Id)
FROM Queryes
GROUP BY Id
SELECT @ID OneValue

Select allOpen in new window

Output

/*------------------------
DECLARE @ID AS INT
SELECT @ID = MIN(Id)
FROM Queryes
GROUP BY Id
SELECT @ID OneValue
------------------------*/
OneValue
-----------
4

(1 row(s) affected)

Select allOpen in new window

Note - Your seconds is not working properly. It will give you any value. Basically Group By in your query is not making any sense. If you remove it then you will get proper value. Do not use Order By in your query - that will decrease the performance. If you want pick the minimum value then use below-

SELECT MIN(Id) FROM Queryes

Select allOpen in new window

Output

/*------------------------
SELECT MIN(Id) FROM Queryes
------------------------*/

-----------
1

(1 row(s) affected)

Select allOpen in new window

If you just want Distinct values then you can also use below -

/*------------------------
SELECT DISTINCT Id FROM Queryes
------------------------*/
Id
-----------
1
2
3
4

(4 row(s) affected)

Select allOpen in new window

Hope it helps!

So, then query2 will give the latest value, meaning the top 1 descending id?

>> Do not use Order By in your query - that will decrease the performance.

Don't assume ORDER BY is always bad. In some cases it's required to guarantee the expected results.  For example, a SELECT TOP X without an ORDER BY is usually meaningless. Granted it probably isn't needed in this specific case, since the query uses aggregates only. However, it all depends on the exact statement involved and the desired results... which aren't clear here.

Does query1 work differently than query2 in sql server 2012?

No. Same way of work but query1 will output to screen and query2 will store the value in a variable.

Does query2 just gets the top 1 row?

Aggregate functions as MIN, MAX, AVG, SUM and COUNT will return always 1 row. None of those need an ORDER BY clause.

>> Aggregate functions as MIN, MAX, AVG, SUM and COUNT will return always 1 row.

@Vitor - Yes, but in this case the queries also include a GROUP BY (for some unknown reason) which cause the results to include more than 1 record.  Without more information, I'm not sure if it was included by accident ... that's why I suggested they clarify the desired vs actual results so someone can assist them with constructing the proper sql statement.

Yes, but in this case the queries also include a GROUP BY (for some unknown reason) which cause the results to include more than 1 record.

Correct. The GROUP BY changes the behavior and for that the query2 will return an error if more that one value is returned because the variable can handle only one value.

Correct. The GROUP BY changes the behavior and for that the query2 will return an error if more that one value is returned because the variable can handle only one value.

>> No Vitor,  Query2 will not return any error. It will assign any random value to the variable.

@Vitor - No, as I mentioned in my first few 3 responses (though not sure if anyone read them ;-)

the 1st query returns all distinct values, and the 2nd populates the variable with what seems like the last value. Though without an ORDER BY I would guess the exact behavior is not guaranteed.

... and

... like I mentioned above, without an ORDER BY,  generally the behavior is not guaranteed.

@amukta - Can you clarify why you are using GROUP BY here? Feels like we're all repeating the same things over and over ..

Thank you very much for the responses, esp. the one with the execution plan!