Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL find maximum of table generated with select Expand / Collapse
Author
Message
Posted Friday, November 23, 2012 8:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 1, 2012 8:20 PM
Points: 2, Visits: 22
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
Post #1388271
Posted Friday, November 23, 2012 10:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:21 AM
Points: 3,595, Visits: 2,702
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
Post #1388277
Posted Friday, November 23, 2012 11:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 7,222, Visits: 12,982
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
Post #1388278
Posted Sunday, November 25, 2012 12:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 1, 2012 8:20 PM
Points: 2, Visits: 22
Tks

Didn't know the WITH yer

Rgds
Post #1388375
Posted Sunday, November 25, 2012 8:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 37,455, Visits: 34,315
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse