Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-24 : 07:55:35
|
| Marco writes "Hi SQLTEAM,Let me explain my problem:I have a table "CAR_MOTOR_STATUS" like this:CAR_ID MOTOR_ID MOTOR_STATUS_ID MOTOR_STATUS_DATE1 137 4 2005-02-21 17:36:51.000120 137 1 2005-02-23 17:36:53.0001 137 3 2005-02-25 17:37:42.0001 137 5 2005-02-27 17:37:43.0001 138 4 2005-02-26 08:56:09.000120 138 1 2005-02-27 17:37:43.000The motor can be in any of these status (MOTOR_STATUS_ID)4=New, 1=Assigned, 3=Maintenance, 5=Repaired. For the current sample, MOTOR_ID=137 first has been New(4), later it has been Assigned toCAR_ID=120, later was taken out from that car and passed to status 3 (Maintenance); at this very moment, this motor has been assigned to CAR_ID 1,a dummy value that means that it has been assigned to a none car. And finally the status for this pump has changed to Repaired(5) and is ready to beassigned to the same car or to another (The dates through all these statuses are in chronological order).While the motor has been repaired, a new one (MOTOR_ID=138) has been assigned to the CAR_ID=120I'd like to know how would be a query that, given the CAR_ID(120), gets the current MOTOR_ID that is Assigned to it. So, the result of the query should be:CAR_ID MOTOR_ID MOTOR_STATUS_ID MOTOR_STATUS_DATE120 138 1 2005-02-27 17:37:43.000because the current MOTOR_ID(138) that has the MOTOR_STATUS_ID = 1 (Assigned) is currently assigned to the CAR_ID=120 Hope all the situation and question is clear, and you are able to help me, Many thanks in Advance,Marco" |
|
|
Chester
Starting Member
27 Posts |
Posted - 2005-02-24 : 09:39:15
|
| select top 1 *from CAR_MOTOR_STATUSwhere CAR_ID = 120 and MOTOR_STATUS_ID = 1order by MOTOR_STATUS_DATE desc |
 |
|
|
|
|
|
|
|
a sql query