Number State
10 CA
20 CA
30 CA
10 NY
20 NY
30 NY
40 NY

If I have two fields - number and state, as the above table, how can I
find the max number from the average values group by state?

I use the following statement to get the averages:
select avg(number) from table group by state

The result:

20
25

But I am having problem get the maximum values from these two numbers
(which should be 25). Could anyone help?

SQL Learner

A few methods:

SELECT MAX(avg_number) AS max_avg_number
FROM (
SELECT state, AVG(number) AS avg_number
FROM Foo
GROUP BY state) AS F;

SELECT TOP 1 state, AVG(number) AS avg_number
FROM Foo
GROUP BY state
ORDER BY avg_number DESC;

SELECT state, avg_number AS max_avg_number
FROM (
SELECT state, AVG(number) AS avg_number,
ROW_NUMBER() OVER(ORDER BY AVG(number) DESC) AS rk
FROM Foo
GROUP BY state) AS F
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com

Here is a variation on Plamen's first suggestion. The difference is
defining the derived table as a CTE (Common Table Expression):

WITH State_Average as
(
SELECT state, AVG(number) AS avg_number
FROM Foo
GROUP BY state
)

SELECT max(avg_number) from State_Average /* This
is the main SELECT which treats the CTE as a table, and will return
the number you want */


I like CTE's a lot because you can separate the derived table code
from the main SELECT statement. It makes the code easier to read and
maintain. This isn't the best example because you're only selecting
against the derived table, so the cte/non-cte difference isn't that
big. But think about cases where you have to join the derived table
to other tables, and maybe some of those other tables are derived.
In those cases, the CTE is a lot easier to read.

Bill

Wow! This is awsome! Thank you so much, Plamen of SQL Studio. These
are really helpful. I learned much more than what I had expected.

My original approach was using the subquery like your first method,
but now, I also learned about other ways for getting the solution.

Just one question though, in method 1, why is the "AS F" as the end
necessary? (It seems like the query will not work without it.)

Thanks again!

SQL Learner

Hi Bill,

Thank you for your help. This method is cool. So the State_Average
is like a temporary table that gets deleted after the select statement
is ran?

(It is great to get so many helps from this group - a group that I can
count on.)

SQL Learner

When you use a derived table (the subquery) you need to provide an alias. The AS keyword is used to designate alias and
F is the alias.

--
Plamen Ratchev
http://www.SQLStudio.com

On Jan 20, 11:11*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Thanks again, Plamen.

So even though the alias "F" is not being used elsewhere, we will
still need it to make the query runable.

SQL Learner

Yes, you do need the alias even if you do not use it. The reason is that a derived table is a table expression and as
such needs a name. The alias provides the name for the derived table.

--
Plamen Ratchev
http://www.SQLStudio.com

On Jan 21, 9:45*am, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
I see. Thank you so much, Plamen!

SQL Learner

SQL Learner (excelmodeling (AT) gmail (DOT) com) writes:
Logically you could think of it that way. However, State_Average is
never materialised, and SQL Server may recast the computation order, as
long as the result is the same.

On the other hand, if the query has multiple references to the CTE, it
is very likely that SQL Server will compute it multiple times.

A better way to think of a CTE is a view which has the scope of a
single query.

CTE:s and derives tables are very similar concepts. The main difference
is that CTE:s are named, they can be referred to multiple times in the
query. (And then there is a special form of a CTE to deal with recursive
structures.)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx