Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    33

    Unanswered: Reporint the last entry...

    Hi,
    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?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Re: Reporint the last entry...

    You 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.

  3. #3
    Join Date
    Nov 2002
    Posts
    33
    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

  4. #4
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31
    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?

  5. #5
    Join Date
    Nov 2002
    Posts
    33
    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

  6. #6
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31
    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.
    Last edited by Cesar Fraustro; 01-09-03 at 17:44.

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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.

  8. #8
    Join Date
    Nov 2002
    Posts
    33
    OK,
    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,
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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")
    Code:
    
    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 
    

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •