SQL find maximum of table generated with select

Question

Post reply

SQL find maximum of table generated with select

Ironicster

Grasshopper

Points: 18
More actions
November 23, 2012 at 8:43 pm

#271148

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sqlnaive SSCoach Points: 17437 More actions · Answer 1

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

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

Ironicster Grasshopper Points: 18 More actions · Answer 3

Ironicster

Grasshopper

Points: 18

November 25, 2012 at 12:46 am

#1562026

Tks

Didn't know the WITH yer

Rgds

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

Ironicster (11/25/2012)
Tks
Didn'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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)