Here's a shot at having 1 view. This is a view I have which returns all rows
from a table Location for the (MAX(InvID))
View: vprodloc
SELECT * FROM products t1,location t2 where t1.productid = t2.productid and
t2.invid = (sel max(invid) from location where productid = t1.productid)
I know
Yes, Thank You Bill, it worked with a couple of key points:
1) I/O Error on the 2nd SELECT prompted an AUTOCHK which prompted a
RELOAD. I must've blowed up the DB in testing (tech jargon).
2) As Bruce Chitiea pointed out, my table is a many-to-many; your view
deconstructs it, which Bruce
Doug,
Does this work?
CREATE VIEW TrailerMaxDates (TrailerID, MaxDate) AS +
SELECT TrailerID,MAX(TrailerDate) FROM TrailerInfo GROUP BY TrailerID
SELECT t1.*, t2.TrailerLoc +
FROM TrailerMaxDates t1, TrailerInfo t2 +
WHERE t1.TrailerID = t2.TrailerID +
AND t1.MaxDate = t2.TrailerDate
Bill
I have a table of trailers, dates and locations.
I'm trying to create a view that shows the trailer ID and the most
recent location.
From the above, the view would display:
TrailerID TrailerLocation
2706 MAD
531002 MKE
A trailer will be at only one location on any
4 matches
Mail list logo
