r/SQLOptimization • • Feb 28 '23

how do I ingrate this CTE?

with highque as(

	select max(ExtendedPrice) highest

	from Sales.InvoiceLines il

	join Sales.Invoices i on il.InvoiceID = i.InvoiceID

	where (InvoiceDate between '1/1/2013' and '12/31/2013')

	group by i.CustomerID

	)

select InvoiceDate, CustomerName

from Sales.Invoices i

join Sales.Customers c on c.CustomerID = i.CustomerID

where (InvoiceDate between '1/1/2013' and '12/31/2013')

order by CustomerName

the CTE finds the largest invoice 2013, the query after finds the customer name and date of invoice, how do I connect the largest invoice to the customer and the date they invoiced?

4 Upvotes

Duplicates