Show employee with highest number of sales-CodePudding

I would like to show the employee with the highest number of sales

i used a subquery to count every employee's sales, and showed the top one, then used that to show that employee's information

SELECT
TOP 1 staff_id, 
COUNT(*) 
AS sales
FROM orders
GROUP BY staff_id 
ORDER BY sales DESC

it shows that the employee with the ID=6 has the highest number of sales but when passing this as a subquery:

SELECT * 
FROM staffs 
WHERE (staff_id =
            
((select top 1 staff_id, count(*) as sales from orders group by staff_id order by sales desc )))

i get the follwing error: **only one expression can be specified in the select when the subquery is not introduced with exists **

CodePudding user response：

Try the following query instead.

This returns a single expression, which the error is telling you is expected:

SELECT * 
FROM staffs 
WHERE staff_id in (
  SELECT TOP (1) staff_id
  FROM orders 
  GROUP BY staff_id
  ORDER BY count(*) DESC
);

You might also want to look at the with_ties clause to handle where there are duplicate counts.