Ironicster (11/23/2012)
Hi guys.Newb in SQL with what i hope is an easy one:
SELECT ACCUM.c1
FROM (SELECT t1.c1, SUM(t1.c2) as adding
FROM Table1 t1
GROUP BY t1.c1) AS ACCUM
WHERE ACCUM.adding = (SELECT MAX(adding) from ACCUM)
When i do this i get: Invalid object name 'ACCUM'.
If i place a number present in ACCUM table instead of (SELECT MAX(adding) from ACCUM), i get a correct result. That seems to tell that the only problem is that ACCUM is not recognized inside the ().
Any thoughts?
Regards
ACCUM is a derived table so you cannot reference that as if it were a concrete table within a separate derived table query.
I think is logically equivalent to what you are after, namely the c1 values with the largest sum of their associated c2 values:
WITH cte AS (SELECT c1,
SUM(c2) as adding
FROM Table1
GROUP BY c1),
cte2 AS (SELECT c1,
RANK() OVER (ORDER BY adding DESC) AS adding_rank
FROM cte)
SELECT c1
FROM cte2
WHERE adding_rank = 1;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato