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 01, 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: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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 01, 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: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(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: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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