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:

I need to records would be like this:

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.

CodePudding user response：

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;

CodePudding user response：

To solve this problem, I just add the column FullDateAlternateKey.