September 22, 2003 at 3:32 pm
What is the best way of getting the first and last record of a group?
I will use Northwind as an example.
Below is code that will display the first OrderId and last OrderID for each Employee in the Northwind Database.
However, what I want is the date of the First and Last Order.
SELECT E.EMPLOYEEID, FIRST_ORDERID, LAST_ORDERID
FROM EMPLOYEES E
LEFT JOIN (SELECT MIN(ORDERID) AS FIRST_ORDERID, EMPLOYEEID FROM ORDERS GROUP BY EMPLOYEEID) O ON E.EMPLOYEEID = O.EMPLOYEEID
LEFT JOIN (SELECT MAX(ORDERID) AS LAST_ORDERID, EMPLOYEEID FROM ORDERS GROUP BY EMPLOYEEID) P ON E.EMPLOYEEID = P.EMPLOYEEID
ORDER BY 1;
Thanks in advance,
Billy
Edited by - bp on 09/22/2003 3:38:19 PM
September 22, 2003 at 5:10 pm
SELECT EmployeeId, MIN(OrderDate) FirstDate, MAX(OrderDate) LastDate
FROM Orders
GROUP BY EmployeeId
ORDER BY EmployeeId
--Jonathan
--Jonathan
September 22, 2003 at 5:19 pm
Thanks Jonathan but it is not what I am looking for.
Min(OrderDate) and Max(OrderDate) only works if you assume that Northwind does not pre-date or post-date their orders, which or may not be the case. How would you do it if instead of the date of the First and Last Order, you wanted the "Freight" amount?
September 22, 2003 at 6:05 pm
quote:
Thanks Jonathan but it is not what I am looking for.Min(OrderDate) and Max(OrderDate) only works if you assume that Northwind does not pre-date or post-date their orders, which or may not be the case. How would you do it if instead of the date of the First and Last Order, you wanted the "Freight" amount?
I prefer to use temporal data rather than identity values when speaking of "first" and "last," but I now understand what you want. How about:
SELECT e.EmployeeId,
(SELECT TOP 1 Freight
FROM Orders
WHERE EmployeeId = e.EmployeeId
ORDER BY OrderID) FirstOrder,
(SELECT TOP 1 Freight
FROM Orders
WHERE EmployeeId = e.EmployeeId
ORDER BY OrderID DESC) Last
FROM Employees e
--Jonathan
--Jonathan
September 23, 2003 at 2:43 am
or
SELECT o.EmployeeId,f.Freight,l.Freight
FROM (SELECT EmployeeId,
MIN(OrderID) AS 'FirstOrderID',
MAX(OrderID) AS 'LastOrderID'
FROM Orders
GROUP BY EmployeeId) o
INNER JOIN Orders f
ON f.EmployeeId = o.EmployeeId
AND f.OrderID = o.FirstOrderID
INNER JOIN Orders l
ON l.EmployeeId = o.EmployeeId
AND l.OrderID = o.LastOrderID
Far away is close at hand in the images of elsewhere.
Anon.
September 23, 2003 at 12:40 pm
I found another way too!
It combines Johnathan's and David's methhod...Johnathan's method includes employees that don't have orders and David's method requires less resources to execute.
SELECT E.EMPLOYEEID, (select freight from orders where orderid = FIRST_ORDERID) x,
(select freight from orders where orderid = LAST_ORDERID) y
FROM EMPLOYEES E
LEFT JOIN (SELECT MAX(ORDERID) AS LAST_ORDERID, MIN(ORDERID) AS FIRST_ORDERID, EMPLOYEEID FROM ORDERS GROUP BY EMPLOYEEID) P ON E.EMPLOYEEID = P.EMPLOYEEID
ORDER BY 1;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply