Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Question: Is the day field in contractupdate the day value of startdate? In other words, does DAY(startdate) = day ? –  RolandoMySQLDBA Nov 11 '13 at 16:45
    
@RolandoMySQLDBA: No, no such equality –  porton Nov 11 '13 at 16:49
    
What does the day field represent, and what is the range of possible values ? –  RolandoMySQLDBA Nov 11 '13 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 '13 at 17:00
    
Sorry I couldn't help. Maybe I am just misinterpreting the question. I withdrew my answer. –  RolandoMySQLDBA Nov 11 '13 at 17:30

1 Answer 1

up vote 1 down vote accepted
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)
;
share|improve this answer
    
Before you accept it, please check to make sure the output is what you are looking for. Is the output correct ??? –  RolandoMySQLDBA Nov 11 '13 at 17:02
    
No, output is wrong. It should provide only one ID per contract –  porton Nov 11 '13 at 17:16
    
You want to see just the MAX(day) per contractid ??? –  RolandoMySQLDBA Nov 11 '13 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 '13 at 17:20
    
Updated my answer... –  RolandoMySQLDBA Nov 11 '13 at 17:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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