sql query question

[ajc001]

I posed this question the other day but cant find the original thread so her it is again.

 

Question:

What query would you construct to generate a report of all the employee names with the date and value of their biggest sale?

 

Sample data for the Employees table

[TABLE=width: 324]

[TR]

[TD]Employee_key

[/TD]

[TD]Surname

[/TD]

[TD]Forename

[/TD]

[/TR]

[TR]

[TD]54944

[/TD]

[TD]Smith

[/TD]

[TD]Dorothy

[/TD]

[/TR]

[TR]

[TD]108395

[/TD]

[TD]Dolzael

[/TD]

[TD]Alan

[/TD]

[/TR]

[TR]

[TD]150478

[/TD]

[TD]Kerzel

[/TD]

[TD]Brittany

[/TD]

[/TR]

[TR]

[TD]201000

[/TD]

[TD]Jones

[/TD]

[TD]Kevin

[/TD]

[/TR]

[TR]

[TD]244850

[/TD]

[TD]Winstone

[/TD]

[TD]James

[/TD]

[/TR]

[TR]

[TD]255655

[/TD]

[TD]Flenn

[/TD]

[TD]Glen

[/TD]

[/TR]

[/TABLE]

 

Sample data for the Sales table

 

[TABLE=width: 415]

[TR]

[TD]i.e. the product that was sold

[/TD]

[TD]i.e. the employee who made the sale

[/TD]

[TD][/TD]

[TD][/TD]

[/TR]

[TR]

[TD]|

[/TD]

[TD]|

[/TD]

[TD][/TD]

[TD][/TD]

[/TR]

[TR]

[TD]Order_key

[/TD]

[TD]Employee_key

[/TD]

[TD]Sale_date

[/TD]

[TD]Value

[/TD]

[/TR]

[TR]

[TD]109887

[/TD]

[TD]54944

[/TD]

[TD]15/06/2012

[/TD]

[TD]2,199.00

[/TD]

[/TR]

[TR]

[TD]216789

[/TD]

[TD]54944

[/TD]

[TD]01/01/2012

[/TD]

[TD]3,502.00

[/TD]

[/TR]

[TR]

[TD]300955

[/TD]

[TD]54944

[/TD]

[TD]31/01/2011

[/TD]

[TD]5,189.00

[/TD]

[/TR]

[TR]

[TD]402000

[/TD]

[TD]108395

[/TD]

[TD]14/05/2009

[/TD]

[TD]2,999.99

[/TD]

[/TR]

[TR]

[TD]511309

[/TD]

[TD]150478

[/TD]

[TD]15/06/2012

[/TD]

[TD]2,199.00

[/TD]

[/TR]

[TR]

[TD]489700

[/TD]

[TD]201000

[/TD]

[TD]01/01/2012

[/TD]

[TD]2,199.00

[/TD]

[/TR]

[TR]

[TD]109887

[/TD]

[TD]244850

[/TD]

[TD]31/01/2011

[/TD]

[TD]2,199.00

[/TD]

[/TR]

[TR]

[TD]109887

[/TD]

[TD]255655

[/TD]

[TD]14/05/2009

[/TD]

[TD]2,199.00

[/TD]

[/TR]

[/TABLE]

 

This is the query that was proposed but it does not work and I dont know why!!!

 

SELECT Max(Sales.Value) AS MaxValue, Employee.Forename, Employee.Surname, Sales.sale_date

FROM Sales

INNER JOIN Employee

ON Sales.Employee_key = Employee.Employee_key

GROUP BY Employee.Forename, Employee.Surname, Sales.Employee_Key, Sales.sale_date

 

It returns the following:-

2999.99 Alan Dolzael 2009-05-14

2199.00 Brittany Kerzel 2012-06-15

5189.00 Dorothy Smith 2011-01-31

3502.00 Dorothy Smith 2012-01-01

2199.00 Dorothy Smith 2012-06-15

2199.00 Glen Flenn 2009-05-14

2199.00 James Winstone 2011-01-31

2199.00 Kevin Jones 2012-01-01

 

It is wrong because it returns 3 rows for Dorothy Smith as opposed to just the one row with the maximum sales value, as required by the original question.

Any ideas how the query needs to be modifed to return only the maximum sales.value for all employees?

 

I should mention that I am running this query in MSSQL Server 2012.

Edited August 15, 2012 by ajc001

[CESIL]

Try this

SELECT DISTINCTROW Max(sales.Value) AS [Max Of Value], employee.Forename, employee.Surname, First(sales.Sale_date) AS [First Of Sale_date]FROM employee LEFT JOIN sales ON employee.Employee_key = sales.Employee_key
GROUP BY employee.Forename, employee.Surname;