Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Unanswered: a Distinct Query

    I have 2 tables as following :

    tbl_Articles
    3 ArticleID int
    0 AuthorID int
    0 ArticleTitle
    0 ArticleText
    0 ArticleDate

    tbl_Authors
    3 AuthorID
    0 AuthorFullName
    0 AuthorEmail
    0 AuthorDescription
    0 AuthorImage

    I want to write a query to see the Authors and their last articles with no distinct values.
    Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate

    If anyone knows the solution i will be glad .
    Thanks from now on

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select AuthorImage, AuthorFullName, ArticleTitle, ArticleDate = aDate
    from tbl_Authors a
    inner join (
    select AuthorID, aDate = max(ArticleDate)
    from tbl_Articles) x
    on a.AuthorID = x.AuthorID
    inner join tbl_Articles b
    on x.AuthorID = b.AuthorID
    and x.aDate = b.ArticleDate
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another version:
    Code:
    select AuthorImage
         , AuthorFullName
         , ArticleTitle
         , ArticleDate 
      from tbl_Authors AUTH
    inner 
      join tbl_Articles ART
        on AUTH.AuthorID 
         = ART.AuthorID
     where ART.ArticleDate
         = ( select max(ArticleDate)
               from tbl_Articles
              where AuthorID 
                  = AUTH.AuthorID )
    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
  •