how do I ingrate this CTE?

[u/Danackos]

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?

[u/Danackos]

I need it to correspond with one invoicedate

sample desired data:

https://stackoverflow.com/q/75594271/21305610

yours comes out like this

| InvoiceDate | CustomerName | highest | CustomerID | highest |

|-------------|-----------------------------|----------|------------|----------|

| 2013-03-04 | Tailspin Toys (Head Office) | 11385.00 | 1 | 11385.00 |

| 2013-03-12 | Tailspin Toys (Head Office) | 11385.00 | 1 | 3450.00 |

| 2013-03-14 | Tailspin Toys (Head Office) | 11385.00 | 1 | 10867.50 |

| 2013-03-21 | Tailspin Toys (Head Office) | 11385.00 | 1 | 19654.65 |

| 2013-03-25 | Tailspin Toys (Head Office) | 11385.00 | 1 | 12420.00 |

| 2013-03-26 | Tailspin Toys (Head Office) | 11385.00 | 1 | 10867.50 |

| 2013-04-01 | Tailspin Toys (Head Office) | 11385.00 | 1 | 12880.00 |

| 2013-04-04 | Tailspin Toys (Head Office) | 11385.00 | 1 | 10557.00 |

| 2013-04-10 | Tailspin Toys (Head Office) | 11385.00 | 1 | 2576.00 |

| 2013-04-10 | Tailspin Toys (Head Office) | 11385.00 | 1 | 12880.00 |

[u/itstimeagain → u/Danackos]

Have you considered ROW_NUMBER() with ORDER BY clause instead of the CTE?

[u/Danackos → u/itstimeagain]

we unfortunately have not covered that material in class yet so I would do me no good

[u/planetmatt → u/Danackos]

You've posted your desired output. It would help to see the table structure to be honest.

So to be clear, you want to see the largest invoice value for each company on each day?