Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: Re:Doubt In Sql Server 2005

    Hi

    Have a glance @ http://img98.imageshack.us/img98/4348/tableo.png

    For a single country there may be n numbers of country_notes.
    I Need to fetch all the distinct records of countries with the latest country_note records.

    we can get the latest country_notes by using createdt.

    Regards
    Eswaramoorthy.S

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT countries.countrycode
         , countries.countryname
         , country_notes.createdt
         , country_notes.notedetails
      FROM countries
    INNER
      JOIN ( SELECT countrycode
                  , MAX(createdt) AS latest
               FROM country_notes
             GROUP
                 BY countrycode ) AS m
        ON m.countrycode = countries.countrycode
    INNER
      JOIN country_notes
        ON country_notes.countrycode = m.countrycode
       AND country_notes.createdt = m.latest
    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
  •