How to group records with Transact-SQL?

Question

I need to group the same records at the database. My code:

SELECT 
    FirstName, LastName, FullDateAlternateKey
FROM 
    ((AdventureWorksDW2012..FactInternetSales 
INNER JOIN 
    AdventureWorksDW2012..DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey)
INNER JOIN 
    AdventureWorksDW2012..DimDate ON DimDate.DateKey = FactInternetSales.DueDateKey)
INNER JOIN 
    AdventureWorksDW2012..DimCustomer ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey
WHERE 
    YEAR(FullDateAlternateKey) LIKE '2013' 
    AND MONTH(FullDateAlternateKey) LIKE '12'

What I get:

FIRSTNAME	LASTNAME	DATE
Eugene	Huang	2013-12-22
Eugene	Huang	2013-12-22
Eugene	Huang	2013-12-22
Eugene	Huang	2013-12-22
Like	Lal	2013-12-22
Like	Lal	2013-12-22
Like	Lal	2013-12-11
Like	Lal	2013-12-11
Like	Lal	2013-12-12
Like	Lal	2013-12-12
Jaclyn	Lu	2013-12-01

I need to records would be like this:

FIRSTNAME	LASTNAME	DATE
Eugene	Huang	2013-12-22
Like	Lal	2013-12-22

and so on.

If I add

GROUP BY FirstName, LastName

I get this error:

The "AdventureWorksDW2012...DimDate.FullDateAlternateKey" column is not allowed in the selection list because it is not contained in either the aggregate function or the GROUP BY sentence.

Answer 1

You want the maximum date per FirstName and LastName, so you GROUP BY FirstName, LastName and select MAX(FullDateAlternateKey). (FullDateAlternateKey is a strange name for a date by the way.)

The join to DimProduct seems superfluous. The date condition can be improved.

SELECT c.firstname, c.lastname, MAX(d.fulldatealternatekey)
FROM adventureworksdw2012.factinternetsales fis
JOIN adventureworksdw2012.dimcustomer c ON c.customerkey = fis.customerkey
JOIN adventureworksdw2012.dimdate d ON d.datekey = fis.duedatekey
WHERE d.fulldatealternatekey >= '2013-12-01'
  AND d.fulldatealternatekey < '2014-01-01'
GROUP BY c.firstname, c.lastname
ORDER BY c.firstname, c.lastname;