Digitalcandy
IS-IT--Management
Your help would be much appreciated.
I have 3 tables.
dbo.TravelerLot
dbo.WorkOrder
dbo.Process
My code is currently like this;
SELECT dbo.WorkOrderLot.LotID, dbo.TravelerLot.TravLotID, dbo.Process.ProcNum
FROM dbo.TravelerLot INNER JOIN
dbo.WorkOrderLot ON dbo.TravelerLot.LotID = dbo.WorkOrderLot.LotID INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID
and it returns the following;
dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 18 24
10 25 100
10 9 13
10 33 256
However, I'm trying to return one record for each dbo.WorkOrderLot.LotID based off of the lowest dbo.Process.ProcNum value in the group. So using the above example I would want my view to return the following record;
dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 9 13
Thanks for your help. Formatting this post was a pain in the buttocks!
I have 3 tables.
dbo.TravelerLot
dbo.WorkOrder
dbo.Process
My code is currently like this;
SELECT dbo.WorkOrderLot.LotID, dbo.TravelerLot.TravLotID, dbo.Process.ProcNum
FROM dbo.TravelerLot INNER JOIN
dbo.WorkOrderLot ON dbo.TravelerLot.LotID = dbo.WorkOrderLot.LotID INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID
and it returns the following;
dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 18 24
10 25 100
10 9 13
10 33 256
However, I'm trying to return one record for each dbo.WorkOrderLot.LotID based off of the lowest dbo.Process.ProcNum value in the group. So using the above example I would want my view to return the following record;
dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 9 13
Thanks for your help. Formatting this post was a pain in the buttocks!