November 23, 2012 at 8:43 pm
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
November 23, 2012 at 10:53 pm
Will this help:
SELECT c1
FROM(SELECT c1, SUM(c2) AS 'Adding',(ROW_NUMBER() OVER (ORDER BY SUM(c2) DESC)) AS 'Row'
FROM Table1 t1
GROUP BY c1) a
WHERE Row = 1
November 23, 2012 at 11:01 pm
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
November 25, 2012 at 12:46 am
Tks
Didn't know the WITH yer
Rgds
November 25, 2012 at 8:02 am
Ironicster (11/25/2012)
TksDidn't know the WITH yer
Rgds
Just to make it easier to find information on the subject, it's the precursor for a thing called "Common Table Expressions" or just "CTE" for short.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply