selecting top record in a query.

Question

Hello there. I am working on northwind database. There are nine employees in the database. Each employee has his&her sales. My aim is to return the top most of the sales they have done. so there will be nine results. below script returns all the sales they did which I could not return the top most sales. select e.firstname+' '+e.lastname as [Worker] p.productname as [Top most saled product] r.quantity*r.unitprice as [Price] from employees as e inner join orders as o on e.employeeid=o.employeeid inner join [order details] as r on r.orderid=o.orderid inner join products as p on p.productid= r.productid where o.shippeddate is null order by [Price] desc go help please.

Kumar Saurabh · Answer

Turkalp If I have understood your question clearly - SQL Query - as per me would go as below- WITH TempTable AS ( Select em.FirstName AS FirstName em.LastName AS LastName pr.ProductName AS ProductName MAX(ord.Quantity * ord.UnitPrice) AS MAXPRICE FROM Employees AS em INNER JOIN Orders AS od ON em.EmployeeID=od.EmployeeID INNER JOIN [Order Details] AS ord on od.OrderID=ord.OrderID INNER JOIN [Products] AS pr ON pr.ProductID=ord.ProductID --WHERE od.ShippedDate IS NULL GROUP BY em.FirstName  em.LastNamepr.ProductName ) SELECT FirstName LastName MAX(MAXPRICE) AS TOPMOSTPRICE FROM TempTable Group by FirstNameLastName Explanation- First Part of the Query gives me the Unique combination of Employees and their all sales based on Employee Name and Product Name. ThenI have used CTE to retrieve the max amount for each of the Employee. Let me know  if this was the desired out put.

Turkalp Kucur · Answer

This is the answer. Thank you!.

Turkalp Kucur · Answer

I actualy did it but as I said one result which is the person who accomplished the biggest salary. What I need is I need to return the each person's best salary. The number of return value will be nine.

Suthish Nair · Answer

You need to find Max of salary and do a group by.

Turkalp Kucur · Answer

hello it returns the only one record... What I need is I need to obtain each employee's best salary. There are nine employees in total. Thus query must return nine top most record.

venkata kumar · Answer

hi fallow the fallowing query select top 1 e.firstname+' '+e.lastname as [Worker] p.productname as [Top most saled product] r.quantity*r.unitprice as [Price] from employees as e inner join orders as o on e.employeeid=o.employeeid inner join [order details] as r on r.orderid=o.orderid inner join products as p on p.productid= r.productid where o.shippeddate is null order by [Price] desc Thanks & Regards Ravi9