Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    8

    Unanswered: Query to extract multiple most recent records ...

    Firstly apologies in advance if this information exists - I have searched and read a number of similar requests, but nothing I can comprehend that will address what I'm looking to do. (Of course it may be my comprehension ... unfortunately no guru at this stuff!)

    I'm trying to work out how to create a query that will output the 3 most recent amounts prior to the current (or selected*) date

    So for example lets assume a single table PAY (it's actually a query result, but that shouldn't make any practical difference as far as I understand things?), and the fields:

    Date (in DD/MM/YYYY format)
    Name
    Amt
    Method

    And data as follows:

    Date Name Amt Method
    01/03/2011 Joe $300 Chq
    01/04/2011 Joe $400 Cash
    01/05/2011 Joe $200 Chq
    01/06/2011 Joe $600 EFT
    01/07/2011 Joe $900 Chq
    01/08/2011 Joe $100 EFT
    01/03/2011 Bob $100 Cash
    01/04/2011 Bob $200 Cash
    01/05/2011 Bob $300 EFT
    01/06/2011 Bob $400 Cash
    01/07/2011 Sue $700 Chq
    01/08/2011 Sue $700 Chq

    What I'm trying to achieve as an output being the 3 most recent amounts is:

    Date Name Amt Method
    01/06/2011 Joe $600 EFT
    01/07/2011 Joe $900 Chq
    01/08/2011 Joe $100 EFT
    01/04/2011 Bob $200 Cash
    01/05/2011 Bob $300 EFT
    01/06/2011 Bob $400 Cash
    01/07/2011 Sue $700 Chq
    01/08/2011 Sue $700 Chq

    That's basically it with the following "nice to have's":

    1) sorting by name
    2) * the ability to do the same as above, but from an arbitrary date. For example if the arbitrary date was: 15/04/2011, then the output would be:

    Date Name Amt Method
    01/03/2011 Joe $300 Chq
    01/04/2011 Joe $400 Cash
    01/03/2011 Bob $100 Cash
    01/04/2011 Bob $200 Cash

    Any assistance, advice, or links to any posts/sites where the answer already exists appreciated.

    sjacko

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t1.Date   
         , t1.Name
         , t1.Amt
         , t1.Method
      FROM daTable as t1
    LEFT OUTER
      JOIN daTable as t2
        ON t1.Name = t2.Name
       AND t1.Date < t2.Date
     WHERE t1.Date <= '15/04/2011'
    GROUP
        BY t1.t1.Date   
         , t1.Name
         , t1.Amt
         , t1.Method
    HAVING COUNT(t2.Name) < 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    8
    Thanks Rudy .... a few tweaks and it's doing exactly what I was hoping for. Now will have a play around it with making it even more 'useful'.

    (And - temperature aside - a touch jealous of where you are ... I know they're a 'touch' east, but would be easier to get to a Great Big Sea gig than from here in Oz!)

Posting Permissions

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