It shows up in the output because it appears in the select clause.
When you use a group by query, you are guaranteed to get distinct values for those in the group by clause. More specifically, when you have multiple columns in the group by, you will get a row in the output for each distinct *combination* of columns in the group by.
When I write queries like this, I try to write it in a way that minimizes the number of columns in the group by clause. If you are careful, and write the query properly, you will get the same results but the query will take less time to execute.
In this case, I would first construct a query that returns the last invoice date for each customer, like this:
Code:
Select Customer,
CustomerName,
Max(InvoiceDate) As [Latest]
From vwsninvoicelinesales
Group By Customer, CustomerName
This will return a single row for each customer along with the max invoice date. I would then combine this with the other query using a derived table technique or a common table expression. Like this:
Derived table method
{code]
select dci.item,
dci.scheduleID,
dci.customer,
LastInvoiceDate.customername,
LastInvoiceDate.[Latest]
from tblsoDiscountCustomerItem as dci
Inner Join (
Select Customer,
CustomerName,
Max(InvoiceDate) As [Latest]
From vwsninvoicelinesales
Group By Customer, CustomerName
) As LastInvoiceDate
On dci.customer = LastInvoiceDate.customer
[/code]
Common table expression method:
Code:
; With LastInvoiceDate As
(
Select Customer,
CustomerName,
Max(InvoiceDate) As [Latest]
From vwsninvoicelinesales
Group By Customer, CustomerName
)
select dci.item,
dci.scheduleID,
dci.customer,
LastInvoiceDate.customername,
LastInvoiceDate.[Latest]
from tblsoDiscountCustomerItem as dci
Inner Join LastInvoiceDate
On dci.customer = LastInvoiceDate.customer
Both of the queries I show will return the same data. Both should execute ever so slightly faster than your method because there are less columns in the group by clause.
Please understand that I am not trying to say that your method is wrong. I'm not. I'm trying to help you to understand while also showing an alternate method that may be a little better.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
