OK, I’m somewhat new to writing SQL queries and came across a problem that seems like it should be easy, but I can’t figure out how to do this.
I have 2 tables with a 1 to many relationships.
The ‘many’ table has a text field and a date field.
I want to report the last (most recent) row added to the 2nd table, by linking the two tables and somehow showing the last text field entered.
How can I do this?
timestamp is an internal type that uses a code for each row, the higher ts mark is, the earlier insert/update was done.
So, add another field to your many table (ts_field timestamp) and perform some inserts/updates and use ts_field as any other field, lets say SELECT * FROM MANY ORDER BY TS_FIELD must show your records in updated/inserted order, ok?
That gives me the records in order, however, I want on my 'report' only the most recent entry for each linked item between the two tables.
So if I have a quote number in table1 and 5 rows that have the same quote number in table2, I want in my join to create only once record-set that will include table1 values and the last record from table2.
from "OneTable" o
join "ManyTable" m on o."id"=m."id"
from "ManyTable" m
group by "id"
) x on m."id"=xx."id"
) xx on m."newid"=xx."newid"
This query is designed for more non-sorting columns than 1 ("text")
Not tested. Post creating query.
I’m thick today…
I could not follow the advice.
Here is a part of the code I’m trying to use. I have included the main table (QuoteMaster) with joins to two other tables (In reality there are several other joins all from the main table).
The QUOTE is a unique key in the main table and repeats in the QuoteNotes table.
The date field is a date/time stamp.
I’m trying to get a single record-set for each quote that will have the latest MEMO field from the QuoteNotes table. There aren’t entries in the notes table for each quote, but they may repeat…
Here is the code:
SELECT QuoteMaster.Quote, QuoteMaster.CustID,
FROM QuoteMaster LEFT OUTER JOIN
[Product Group] ON
QuoteMaster.ProdGrp = [Product Group].ProdGrp LEFT OUTER
QuoteMaster.Quote = QuoteNotes.Quote
1. I wrote "Not tested. Post creating query." and you have posted nothing. I mean DDL script (CREATE TABLES, PK, FK).
I still do not know, what PK has your QuoteNotes. (Quote,"date"),(Quote,"date",Memo) are near candidate keys,
but can duplicities be there? Should I reverse engineer your design?
2. So I assume QuoteNotes PK(Quote,"date")
CREATE TABLE [Product Group] (ProdGrp int primary key,PDescr varchar(8000))
CREATE TABLE QuoteMaster(Quote int primary key,CustID int null,ProdGrp int null
,foreign key (ProdGrp) references [Product Group](ProdGrp))
CREATE TABLE QuoteNotes (Quote int not null,"date" datetime not null,Memo varchar(8000) not null
,primary key (Quote,"date"),foreign key (Quote) references QuoteMaster(Quote))
SELECT qm.Quote, qm.CustID, qn2.Memo, qn2."Date", pg.PDescr
from QuoteMaster qm
left join QuoteNotes qn2 on qm.Quote=qn2.Quote
from QuoteNotes qn1
group by qn1.Quote
) X on qn2.Quote=X.Quote and qn2."date"=X."date"
left join [Product Group] pg on qm.ProdGrp = pg.ProdGrp