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]
<mailto:[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
------------------------------------------------------------------------
Avast logo <https://www.avast.com/antivirus>
This email has been checked for viruses by Avast antivirus software.
www.avast.com <https://www.avast.com/antivirus>
<#m_-3840436472341346786_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
<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]
<mailto:[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
<https://groups.google.com/d/msgid/rbase-l/ed5bcad6-62c8-c38c-8725-3a886327dcbe%40wi.rr.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
<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]
<mailto:[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
<https://groups.google.com/d/msgid/rbase-l/CAKfk%3DFpK976sZT6foi2vd%3DmdJAazWj9sDbU%3DgAFiceq41xXzSw%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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.
