0

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract).

contractupdate also has DATE field startdate and TINYINT field day.

I want the list of contract IDs ordered by the value of field day in the latest (having maximal startdate) contract update which refers to this contract.

MySQL.

5
  • Question: Is the day field in contractupdate the day value of startdate? In other words, does DAY(startdate) = day ? Nov 11, 2013 at 16:45
  • @RolandoMySQLDBA: No, no such equality
    – porton
    Nov 11, 2013 at 16:49
  • What does the day field represent, and what is the range of possible values ? Nov 11, 2013 at 16:50
  • @RolandoMySQLDBA: It is not relevant to my question what is day. Well, I'll answer your question: day: 1..28 is the day of month, in every month at every day with this number a payment should be made
    – porton
    Nov 11, 2013 at 17:00
  • Sorry I couldn't help. Maybe I am just misinterpreting the question. I withdrew my answer. Nov 11, 2013 at 17:30

1 Answer 1

1
SELECT B.contractid,B.startdate,B.day
FROM
    (SELECT MAX(startdate) startdate,contractid
    FROM contractupdate GROUP BY contractid) A
    INNER JOIN
    contractupdate B USING (contractid,startdate)
;
8
  • Before you accept it, please check to make sure the output is what you are looking for. Is the output correct ??? Nov 11, 2013 at 17:02
  • No, output is wrong. It should provide only one ID per contract
    – porton
    Nov 11, 2013 at 17:16
  • You want to see just the MAX(day) per contractid ??? Nov 11, 2013 at 17:19
  • No, I want to see the unique value day for the contract update with greatest startdate (having fixed a contract ID). And so for every contract ID.
    – porton
    Nov 11, 2013 at 17:20
  • Updated my answer... Nov 11, 2013 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.