CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE

Author

Topic

Magi
Starting Member

2 Posts

Posted - 2013-12-16 : 14:13:37

can some one show me how to rwite the following query using the CTE
SELECT SUM(InvoiceMax) AS SumOfMaximums
FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax
FROM Invoices
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
GROUP BY VendorID) AS MaxInvoice

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-16 : 14:27:16

[code]
;with MaxInvoice (VendorID, InvoiceMax)
as
(
SELECT VendorID, MAX(InvoiceTotal)
FROM Invoices
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
GROUP BY VendorID
)
SELECT SUM(InvoiceMax) AS SumOfMaximums
FROM MaxInvoice
[/code]

Be One with the Optimizer
TG

Magi
Starting Member

2 Posts

Posted - 2013-12-16 : 16:20:09

Thank you so much I am still doing some reading on how to use it. Thanks again for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:33:48

[code]
SELECT SUM(InvoiceMax)
FROM
(
SELECT VendorID, InvoiceTotal AS InvoiceMax,ROW_NUMBER() OVER (PARTITION BY VendorID ORDER BY InvoiceTotal DESC) AS Rn
FROM Invoices
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources