CODEWITHSUNDEEP
sqlsql-server-2008
xenapan
xenapan

Reputation: 87

Finding the DETAILS of rows with max/min values.

So I want to find the min/max values for each employee along with some of the details (for now lets just say the order date) of those orders except all I get is a bunch of duplicate rows with wrong values

select EMP.SaleID,EMP.Name, MinOrder.OrderDate, min(MinOrder.OrderTotal) , [MinOrder.Otherdetails], MaxOrder.OrderDate, Max(MaxOrder.OrderTotal) ,[MaxOrder.Otherdetails]
from Employees as EMP
inner join Orders as MinOrder on MinOrder.SaleID = EMP.SaleID
inner join Orders as MaxOrder on MaxOrder.SaleID = EMP.SaleID
group by Sales.Account,  MinOrder.OrderDate,  MaxOrder.OrderDate,  [MinOrder.Otherdetails],[MaxOrder.Otherdetails]

Employees Table:

SaleID  Name etc
  1     Jim
  2     Bob

Orders Table:

OrderID SaleID OrderDate OrderTotal [OtherDetails]
1        1      1/1/14    1000        ...
2        1      2/2/13    2000        ...
3        1      1/3/13    3000        ...
4        2      2/2/12    1000        ...

What I want is 

SaleID Name MinOrderDate MinOrderTotal MaxOrderDate MaxOrderTotal OtherMax/Mindetails
1      Jim  1/1/14        1000          1/3/13          3000       ...
2      Bob  2/2/12        1000          2/2/12          1000       ...

Upvotes: 0

Views: 45

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Maybe not the most straight forward approach, but you could use a common table expression to rank the rows, and a plain join to get the data for the min/max row;

WITH cte AS (
  SELECT e.saleid, name, orderid, orderdate, ordertotal, 
  ROW_NUMBER() OVER (PARTITION BY name ORDER BY ordertotal)      rn1,
  ROW_NUMBER() OVER (PARTITION BY name ORDER BY ordertotal DESC) rn2
  FROM employees e JOIN orders o
    ON e.saleid = o.saleid
)
SELECT c1.saleid, c1.name, 
  c1.orderdate minorderdate, c1.ordertotal minordertotal,
  c2.orderdate maxorderdate, c2.ordertotal maxordertotal
FROM cte c1
JOIN cte c2
  ON c1.saleid=c2.saleid
WHERE c1.rn1=1 AND c2.rn2=1
ORDER BY saleid;

An SQLfiddle to test with.

Upvotes: 1

Related Questions