Results 1 to 2 of 2

Thread: get last record

  1. #1
    Join Date
    Nov 2010
    Posts
    4

    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
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.Collection
         , a.frabilgimjer
         , a.corbustackle
         , b.RecordDate
         , b.scrimflabat
      FROM gromulphastic AS a
    INNER
      JOIN ( SELECT Collection
                  , MAX(RecordDate) AS LastDate
               FROM quistipunctous
             GROUP
                 BY Collection ) AS m
        ON m.Collection = a.Collection
    INNER
      JOIN quistipunctous AS b
        ON b.Collection = a.Collection
       AND b.RecordDate = m.LastDate
    rudy.ca | @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
  •