I'm trying to write a query that will show me the last record inside of another record. In this scenario i have a database record and attached to it is one or more records. I need to get the record from the first table and the last record inserted in the last table. The tables are joined with a collection:
Table A --> Collection -- Table B.
I need to get information from table A and the last Record Date in table B along with another field that's in table B.
I've tried to use several different methods, but each time I get duplicated results for the records in table A that have multiple records in table B. I used the Max function to get the last record date in table B, but because I need to get the other field from table B the group by causes the query to pull all records, not just the last one.
FROM gromulphastic AS a
JOIN ( SELECT Collection
, MAX(RecordDate) AS LastDate
BY Collection ) AS m
ON m.Collection = a.Collection
JOIN quistipunctous AS b
ON b.Collection = a.Collection
AND b.RecordDate = m.LastDate