Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Unanswered: TOP for multiples.

    Something like this may have been posted before. However, I have not seen a clear answer. So here it goes.

    I have a Client_tbl and a Payment_tbl
    Client_tbl is similair to this.

    ClientID First Last
    1 Bob Smith
    2 Jenny Jones


    Payment_tbl looks like this
    PayID ClientID paydate payamount
    1 2 01/02/05 15.00
    2 1 01/03/05 11.00
    3 2 01/06/05 15.00
    4 1 01/06/05 2.00
    5 1 02/01/05 12.00
    6 2 02/02/05 8.00
    7 1 02/03/05 14.00

    Now what I need is to pull the two most recent payments from each client. So my results would, in this case, be.

    1 Bob Smith 02/03/05 14.00
    1 Bob Smith 02/01/05 12.00
    2 Jenny Jones 02/02/05 8.00
    2 Jenny Jones 01/06/05 15.00

    I am familiar with TOP but, I can only get the two most recent records with it not the two most recent for each client.
    Any ideas on the best way to do this? Is there an SQL solution for this?
    Not anything I need right away but a problem I discovered I may need an answer to.

    Thanks.

    PS: I know my naming convention isn't standard but it is consistent and it works well for me.
    Darasen

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    GROUP BY ... Try it you might like it ...

    SELECT TOP 2 blah ... FROM Blah ... GROUP BY blah ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    I am uncertain about the Grand Poobah-ness remaining in tact.
    I used the following SQL
    Code:
    SELECT TOP 2 Client_tbl.ClientID, Client_tbl.Last, Client_tbl.First, payment_tbl.paydate, payment_tbl.Payamount
    FROM Client_tbl INNER JOIN payment_tbl ON Client_tbl.ClientID = payment_tbl.ClientID
    GROUP BY Client_tbl.ClientID, Client_tbl.Last, Client_tbl.First, payment_tbl.paydate, payment_tbl.Payamount;
    but it still just gives me the 2 most recent results as opposed to the 2 most recent for each client.
    I will attach the DB for the curious.
    Attached Files Attached Files
    Darasen

  4. #4
    Join Date
    Feb 2004
    Posts
    126
    Just use

    GROUP BY Client_tbl.ClientID

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Darasen
    I am uncertain about the Grand Poobah-ness remaining in tact.
    I used the following SQL
    Code:
    SELECT TOP 2 Client_tbl.ClientID, Client_tbl.Last, Client_tbl.First, payment_tbl.paydate, payment_tbl.Payamount
    FROM Client_tbl INNER JOIN payment_tbl ON Client_tbl.ClientID = payment_tbl.ClientID
    GROUP BY Client_tbl.ClientID, Client_tbl.Last, Client_tbl.First, payment_tbl.paydate, payment_tbl.Payamount;
    but it still just gives me the 2 most recent results as opposed to the 2 most recent for each client.
    I will attach the DB for the curious.
    Well, I am feeling a bit under the weather ... I know that this specific issue has been posted on in the past. Try doing a search ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Feb 2004
    Posts
    126
    Does he need:

    ORDER BY payment_tbl.paydate DESC

  7. #7
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Quote Originally Posted by grrr223
    Just use

    GROUP BY Client_tbl.ClientID
    Problem with that is that when the query is ran with just that you get an error that states that " You tried to executa a query that does not include the specified expression 'last' as part of an aggreagte function"

    M. Owen I AM just joking
    I did look around a bit but really have not found a good answer.
    Darasen

  8. #8
    Join Date
    Feb 2004
    Posts
    126
    Ahhhh, yes, you are correct aren't you .

    I just started playing with this a little in Query Analyzer and wow, I had forgotten how complicated things get whenever you start using GROUP BY.

    Unfortunately, I can't really think of anything at the moment.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Darasen
    Problem with that is that when the query is ran with just that you get an error that states that " You tried to executa a query that does not include the specified expression 'last' as part of an aggreagte function"

    M. Owen I AM just joking
    I did look around a bit but really have not found a good answer.
    Well boys and girls ... A little bit of searching yielded: http://www.dbforums.com/t802112.html HINT: Look at Rudy's responses ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2004
    Posts
    126
    Thanks M Owen, Again, subqueries to the rescue.

    I had started looking at subqueries but got discouraged by having to use GROUP BY, but if you use a subquery, you don't even need GROUP by because it's already taken care of.

  11. #11
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Yes, you are still the Grand Poobah !

    I played around with subqueries before but, kept getting errors.

    I looked but somehow missed that post. I probally did not look far enough back.

    I ended up running the top query on the select query with the join because I did not know how to make it work other wise.

    So not be a pain could someone (rudy?) explain how/why this works?
    Darasen

  12. #12
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by Darasen
    Something like this may have been posted before. However, I have not seen a clear answer. So here it goes.

    I have a Client_tbl and a Payment_tbl
    Client_tbl is similair to this.

    ClientID First Last
    1 Bob Smith
    2 Jenny Jones


    Payment_tbl looks like this
    PayID ClientID paydate payamount
    1 2 01/02/05 15.00
    2 1 01/03/05 11.00
    3 2 01/06/05 15.00
    4 1 01/06/05 2.00
    5 1 02/01/05 12.00
    6 2 02/02/05 8.00
    7 1 02/03/05 14.00

    Now what I need is to pull the two most recent payments from each client. So my results would, in this case, be.

    1 Bob Smith 02/03/05 14.00
    1 Bob Smith 02/01/05 12.00
    2 Jenny Jones 02/02/05 8.00
    2 Jenny Jones 01/06/05 15.00

    I am familiar with TOP but, I can only get the two most recent records with it not the two most recent for each client.
    Any ideas on the best way to do this? Is there an SQL solution for this?
    Not anything I need right away but a problem I discovered I may need an answer to.

    Thanks.

    PS: I know my naming convention isn't standard but it is consistent and it works well for me.

    Ok, starting with these sample tables:

    Code:
    CREATE TABLE Client_tbl
    (ClientID              AUTOINCREMENT
    ,First                 TEXT(48)
    ,Last                  TEXT(48)
    ,CONSTRAINT pk_Client_tbl PRIMARY KEY (ClientID)
    )
    
    CREATE TABLE payment_tbl
    (PayID                 AUTOINCREMENT
    ,ClientID              LONG          NOT NULL
    ,paydate               DATETIME      NOT NULL
    ,payamount             CURRENCY      NOT NULL
    ,CONSTRAINT pk_payment_tbl PRIMARY KEY (PayID)
    ,CONSTRAINT fk_payment_tbl_Client_tbl
                FOREIGN KEY (ClientID)
                REFERENCES Client_tbl (ClientID)
    )

    I loaded the sample data.

    Here’s a non-TOP Query for you:

    Code:
      SELECT Client_tbl.ClientID
            ,Client_tbl.Last
            ,Client_tbl.First
            ,payment_tbl.paydate
            ,payment_tbl.Payamount
        FROM Client_tbl
             INNER JOIN
             payment_tbl
          ON Client_tbl.ClientID = payment_tbl.ClientID
       WHERE payment_tbl.paydate IN
             (SELECT p2.paydate
                FROM payment_tbl as P2
               WHERE P2.ClientID = payment_tbl.ClientID
                 AND P2.paydate =
                     (SELECT MAX(P3.paydate)
                        FROM payment_tbl as P3
                       WHERE P3.ClientID = payment_tbl.ClientID)
                  OR P2.paydate =
                     (SELECT MAX(P4.paydate)
                        FROM payment_tbl AS P4
                       WHERE P4.ClientID = payment_tbl.ClientID
                         AND P4.paydate < 
                             (SELECT MAX(P5.paydate)
                                FROM payment_tbl AS P5
                               WHERE P5.ClientID = payment_tbl.ClientID))))
    GROUP BY Client_tbl.ClientID
            ,Client_tbl.Last
            ,Client_tbl.First
            ,payment_tbl.paydate
            ,payment_tbl.Payamount;

    Ok, that appears to produce the correct results.

    I tested it for like 5 minutes, though, and you should investigate further.


    Sincerely,

    Chris O.

Posting Permissions

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