Results 1 to 2 of 2

Thread: get last record

  1. #1
    Join Date
    Nov 2010

    Unanswered: get last record

    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.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT a.Collection
         , a.frabilgimjer
         , a.corbustackle
         , b.RecordDate
         , b.scrimflabat
      FROM gromulphastic AS a
      JOIN ( SELECT Collection
                  , MAX(RecordDate) AS LastDate
               FROM quistipunctous
                 BY Collection ) AS m
        ON m.Collection = a.Collection
      JOIN quistipunctous AS b
        ON b.Collection = a.Collection
       AND b.RecordDate = m.LastDate | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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