Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: get last record for each field

    I have 2 tables, one is a customer table table and the next is a payment table, I would like to grab the last payment for each customer. It's a one to many relationship. I can only seem to get the first payment for each customer. How can this be achieved in 1 query?

  2. #2
    Join Date
    Nov 2003
    Posts
    8

    Re: get last record for each field

    Originally posted by lynx_lynx
    I have 2 tables, one is a customer table table and the next is a payment table, I would like to grab the last payment for each customer. It's a one to many relationship. I can only seem to get the first payment for each customer. How can this be achieved in 1 query?

    do you have a date assoicated with the payments? or a number?

    if so then look up jion statments

    or describe your DB stucture

  3. #3
    Join Date
    Nov 2003
    Posts
    6

    Re: get last record for each field

    Originally posted by deRusett
    do you have a date assoicated with the payments? or a number?

    if so then look up jion statments

    or describe your DB stucture
    The customers table contains unique customer_id(varchar) and name.
    The payment table has a unique payment_id(vc), customer_id, date, payment_amount.

    I can retrieve the max date or max payment_id of a payment for each customer, yet cannot get the last payment_amount. Any solutions. Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    8
    could you not just call the newest date entry for a costomers purchace?

    then print it


    select payment_id from paymenttable where date=some date function


    sorry I don't use the date funtions much to know all of them

    only date thing I use is CURDATE()

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lynx_lynx, what you are asking for is 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field and if you're not on mysql 4.1 where you can use a subquery, then you will have to use a temp table

    rudy
    http://r937.com/

  6. #6
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    It can be done without a temporary table using a self-join. I am surprised that the documentation mentions the MAX-CONCAT trick as the only way to do it in a single query, especially with the knowledge that it is inefficient.

    The query that you are looking for is

    SELECT name, t1.date, t1.payment_amount
    FROM customers, payment t1, payment t2
    WHERE customers.customer_id = t1.customer_id AND
    t1.customer_id = t2.customer_id
    GROUP BY name, t1.date, t1.payment
    HAVING t1.date = MAX(t2.date);

    This will return the customer's name, the date of their last payment and the amount. You might have to do some changes to the query to make it work in your database, so be sure that if you are selecting any fields, they should come from table t1 and the GROUP BY clause needs to mention them.

  7. #7
    Join Date
    Nov 2003
    Posts
    6
    aus, that worked perfectly, 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
  •