selecting top record in a query.

Turkalp Kucur

14y3.7kSQL

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.}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kumar Saurabh14y ago
Accepted 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.LastName,pr.ProductName
)
SELECT
FirstName,
LastName,
MAX(MAXPRICE) AS TOPMOSTPRICE
FROM TempTable
Group by FirstName,LastName

Explanation- First Part of the Query gives me the Unique combination of Employees and their all sales based on Employee Name and Product Name.

Then,I have used CTE to retrieve the max amount for each of the Employee.

Let me know , if this was the desired out put.
Turkalp Kucur14y ago
This is the answer. Thank you!.
Turkalp Kucur14y ago
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 Nair14y ago
You need to find Max of salary and do a group by.
Turkalp Kucur14y ago
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 kumar14y ago
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