SQL Select the highest sale for the current month

Question

I am trying to Select the name, email address that has the highest individual sale for the current month but for some reason my select statement won't return the right date or the highest amount. Any guidance or help on right direction would be appreciated

Select 
    FirstName, LastName,Email, SaleDate, Max(Total)as HighestTotal
From 
    Sale, Employee
where 
    month(SaleDate) = 12    
Group by 
    SaleDate, FirstName, LastName, Email

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Mar 11 '15 at 20:12
See, you didn't specify dbms, and now you got at least two product specific answers (for different products...) Save yourself and others some time, tag properly in the future! — jarlh, Mar 12 '15 at 08:46

Pரதீப் · Accepted Answer · 2015-03-11T19:35:50.643

Use TOP 1 with ORDER BY HighestTotal desc to get the highest individual sale for the given month

Also use proper INNER JOIN syntax to join two tables. Considering that both the tables have emp_id as common column

SELECT TOP 1 WITH ties E.FirstName,
                       E.LastName,
                       E.Email,
                       S.SaleDate,
                       S.Total AS HighestTotal
FROM   Sale S
       INNER JOIN Employee E
               ON E.emp_id = S.Emp_id
WHERE  Month(S.SaleDate) = 12
ORDER  BY HighestTotal DESC

With Ties will bring more than one employee in case of tie in highest individual sale.

score 0 · Answer 2 · answered Mar 11 '15 at 19:29

0

1) You have to add a condition for the current year:

where month( SaleDate) = 12 AND year(SaleDate) = 2015

2) You have to add ORDER BY to get the highest value:

ORDER BY HighestTotal DESC
LIMIT 1

answered Mar 11 '15 at 19:29

engine9pw

364
1
5

SQL Select the highest sale for the current month

2 Answers2