Hi,
OK, Im somewhat new to writing SQL queries and came across a problem that seems like it should be easy, but I cant 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?
ThanksYou can use a timestamp field in your 'many' table and use a MAX(ts_field) in your query, you will find more information on that topic in BOL.|||OK,
I must be missing something...
Here is the code for the 2nd table:
SELECT MAX(date), memo
FROM TABLE2
GROUP by memo
This does not work... how can I insure I select only the MAX date?
Thanks|||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.
Thanks|||try:
select value_field, max(date_field)
from many
where timestamp_field = (select max(timestamp_field)
from many m
where many.value_field = m.value_field)
group by value_field
note that max(date_field) has nothing to do with timestamp, is just like min(date_field) because the group by clause.
the trick is in the timestamp field.
later you can add the ONE table.|||Bad design=bad result. Why are you using 1:N when you need 1:1 ?
Use TR on {active table} to track changes to {audit table}.
/*
"OneTable" - "id" PK
"ManyTable" - "newid" PK,"id", "text", "date"
*/
select m."id",xx."date",xx."newid",m."text",o.*
from "OneTable" o
join "ManyTable" m on o."id"=m."id"
join
(
select m."id",x."date","newid"=max("newid")
from "ManyTable" m
join
(
select "id","date"=max("date")
from "ManyTable"
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.|||OK,
Im thick today
I could not follow the advice.
Here is a part of the code Im 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.
Im trying to get a single record-set for each quote that will have the latest MEMO field from the QuoteNotes table. There arent entries in the notes table for each quote, but they may repeat
---
Here is the code:
---
SELECT QuoteMaster.Quote, QuoteMaster.CustID,
QuoteNotes.Memo, QuoteNotes.Date,
[Product Group].PDescr
FROM QuoteMaster LEFT OUTER JOIN
[Product Group] ON
QuoteMaster.ProdGrp = [Product Group].ProdGrp LEFT OUTER
JOIN
QuoteNotes ON
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
left join
(
select qn1.Quote,"date"=max(qn1."date")
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
