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 you can do a view that references the same table twice, so can you do something like: SELECT * FROM trailerinfo t1, trailerinfo t2 where t1.trailerid = t2.trailerid and t2.trailerdate = (sel max(trailerdate) from trailerinfo where trailerid = t1.trailerid) Just thinking out loud Karen -----Original Message----- From: Doug Hamilton <[email protected]> To: [email protected] Sent: Tue, Sep 14, 2021 2:18 pm Subject: Re: [RBASE-L] - SQL: Column Value for MAX(Date) 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 suggested, and I was trying to accomplish in one command. 3) I created a view from your 2nd SELECT which provides the results I need - I will use that. 4) I'll still hack away and try to do it in one command.... Tnx again, Doug On 9/14/2021 12:53 PM, Bill Downall wrote: 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 On Tue, Sep 14, 2021 at 1:32 PM Doug Hamilton <[email protected]> wrote: 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 given date. Multiple trailers can be at a location on a date, e.g. on 8/1/21, both trailers were in MKE. The view would display: TrailerID TrailerLocation 2706 MKE 531002 MKE TrailerInfoID is an autonumber column to uniquely identify each row. This SELECT gives me the expected TrailerID and TrailerDate: SELECT TrailerID,MAX(TrailerDate) FROM TrailerInfo GROUP BY TrailerID TrailerID MAX (Trail ---------- ---------- 2706 09/13/2021 531002 08/01/2021 But I'm having trouble getting the location. TIA, Doug | | This email has been checked for viruses by Avast antivirus software. www.avast.com | -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/ed5bcad6-62c8-c38c-8725-3a886327dcbe%40wi.rr.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CAKfk%3DFpK976sZT6foi2vd%3DmdJAazWj9sDbU%3DgAFiceq41xXzSw%40mail.gmail.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/4b816d1a-5dbc-62cc-ea66-86887100f268%40wi.rr.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/1263751281.54812.1631652352287%40mail.yahoo.com.
