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: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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 @ 6:36 PM
Points: 7,097, Visits: 12,600
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:51 PM
Points: 36,959, Visits: 31,469
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
Posted Monday, November 26, 2012 5:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 1,945, Visits: 2,900
You might want to read some basic articles to get you started.

Data basics:
http://www.sqlservercentral.com/stairway/72899/

Database design:
http://www.sqlservercentral.com/stairway/72400/

SQL has pretty much the same scoping rules as any block structured language. Looking at your query, where is the Accum derived table created? Now look at where the table is referenced.

SELECT Accum.c1
FROM (SELECT T1.c1, SUM(T1.c2) AS c1_tot
FROM Table1 AS T1
GROUP BY T1.c1) AS Accum (c1, c1_tot)
WHERE Accum.c1
= (SELECT MAX(c1_tot) FROM Accum);

However, if you create the table at a higher scope, you can make this work. You could put it in a VIEW or CTE.

WITH
Accum (c1, c2_tot)
AS
(SELECT T1.c1, SUM(T1.c2)
FROM Table1 AS T1
GROUP BY T1.c1)
SELECT Accum.c1
FROM Accum
WHERE Accum.c1
= (SELECT MAX(c2_tot) FROM Accum);


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1388565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse