Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    12

    Unanswered: Getting the 2 latest entries of each Item

    Say I have a table look something like this (simplified)

    {A, 1/1/2007}
    {A, 1/2/2007}
    {A, 1/12/2007}
    {B, 2/9,2007}
    {B, 1/1/2007}
    {B, 1/2/2007}
    {C, 1/12/2007}
    {C, 2/9,2007}

    Is it possible to write a qury that list out the 2 latest entries of each item?

    Ex:

    A, 1/12/2007, 1/2/2007
    B, 2/9/2007, 1/2/2007
    C, 2/9/2007, 1/12/2007

    Thanks in advance

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You could easily get the 1 last item by doing a summization query and set it to group on whatever field holds A, B, C. Bring the date field in the query and set it to "Last". Not sure on the last 2 items though as this only gets you the last item for each group (i.e. A, B, and C.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select letter
         , date
      from daTable as T
     where 2 >
           ( select count(*)
               from daTable
              where letter = T.letter
                and date > T.date )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2007
    Posts
    12
    Works great, thanks!!

Posting Permissions

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