Grouping help please 3

[Digitalcandy]

MS SQL 2000

I have two tables, dbo.TravelerLot and dbo.Process. Process has a "many to one" relationship with TravelerLot.



dbo.TravelerLot INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID




I am trying to make a view that has grouping. I'm having a little trouble with the code to make the grouping the way I want.

Without grouping I have something like so;

dbo.TravelerLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
1 100 5
1 101 10
1 102 3
1 103 30
2 220 500
2 210 23
2 240 400
3 45 100
3 46 90



With the propper grouping I'd like my view to look like the example below based on the data example above, (data is returned based on the lowest ProcNum);


dbo.TravelerLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
1 102 3
2 210 23
3 46 90





I need the grouping to happen based on TravelerLot.LotID, however, I also need TravelerLot.TravLotID to be visible but it needs to return the the TravLotID that has the lowest Process.ProcNum value.

Can one of you experts make this happen? I don't know much about SQL programming but this would really help for report writing.

TIA.

 

[Digitalcandy]

I got my solution from the software vendor. Some of you were very close.



SELECT TOP 100 PERCENT dbo.TravelerLot.LotID, dbo.TravelerLot.TravLotID, dbo.Process.ProcNum
FROM dbo.TravelerLot INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID
WHERE (dbo.TravelerLot.TravLotID =
(SELECT TOP 1 stl.TravlotID
FROM travelerlot stl JOIN
process spc ON spc.processid = stl.currentprocid
WHERE stl.lotid = dbo.TravelerLot.lotid
ORDER BY spc.procnum))

 

[ESquared]

It's simpler than that:

SELECT LotID, TravLotID, MinProc
   FROM Traveler T INNER JOIN
   (
      SELECT LotID, MinProc = Min(ProcNum)
      FROM TravelerLot
      INNER JOIN Process ON TravelerLot.CurrentProcID = Process.ProcessID
   ) M ON T.LotID = M.LotID

It's possible that the version you are using will be as efficient as this one because the query engine is very smart about optimizing. However, doing these things via joins instead of correlated subqueries (which is what you've got up there) is better in my mind. I find it easier to understand, and it's not impossible it's more efficient. I am certain that given enough complexity the query optimizer would eventually fail to make the two methods identical.

The vendor's query logically does: for EACH row execute a query to find the lowest matching procID.

What the group by query logically does: find the lowest procID for all the rows in the whole Process table ONCE, then join to the matching row in the TravelerLot table ONCE.

-------------------------------------
A sacrifice is harder when no one knows you've made it.