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

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;

Ironicster (11/25/2012)

---

Tks

Didn't know the WITH yer

Rgds