| Author |
 Topic  |
|
|
karrojo
Starting Member
26 Posts |
 Posted - 05/30/2012 : 02:50:36
|
Good Day!
I have two tables. Master table (contains the main info of a person) and Updates Table (all updates done in a record). From the Master Table, i need to get the latest Update in the UPdates table.
i have this query but gives me null result:
select a.record_no, a.remarks, a.d_updated,
b.record_no, b.remarks, b.d_updated
from master_record a
left join (select top 1 record_no, reason, d_updated
from checks_table
order by d_updated desc) b
on a.record_no = b.record_no
Result:
Record NO Remarks Date_Updated Record_no Remarks Date_updated
1 xxxx 01/01/2012 null null null
2 xxxx 03/01/2012 null null null
i really need all your help. thank you very much!
tobee |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 05/30/2012 : 03:35:24
|
Someting is odd.
b.remarks doesn't exist in the derived table so this should give an error.
A bigger proble uis that there is only one row from the derived table so you will only ever get one record_id row.
I suspect you might want something like
select a.record_no, a.remarks, a.d_updated,
b.record_no, b.remarks, b.d_updated
from master_record a
left join (select record_no, reason, d_updated, remarks
from checks_table t
join (select record_no, d_updated = max(d_updated) from checks_table group by record_no) t2
on t.record_no = t2.record_no
and t.d_updated = t2.d_updated) b
on a.record_no = b.record_no
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nigelrivett on 05/30/2012 03:36:31 |
 |
|
|
karrojo
Starting Member
26 Posts |
Posted - 05/30/2012 : 04:13:33
|
thanks very much NIgelRivett!... I got the result correctly!... more power to YOu and SQLTeam!
thanks again! ^_^ |
|
|
| |
Topic |
|
|
|
Getting the latest date on another table
