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 |
|
karrojo
Starting Member
26 Posts |
 Posted - 2012-05-30 : 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_updatedfrom 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_noResult:Record NO Remarks Date_Updated Record_no Remarks Date_updated1 xxxx 01/01/2012 null null null2 xxxx 03/01/2012 null null null i really need all your help. thank you very much!tobee |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 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 likeselect a.record_no, a.remarks, a.d_updated, b.record_no, b.remarks, b.d_updatedfrom master_record a left join (select record_no, reason, d_updated, remarksfrom checks_table tjoin (select record_no, d_updated = max(d_updated) from checks_table group by record_no) t2on t.record_no = t2.record_noand t.d_updated = t2.d_updated) bon 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. |
 |
|
|
karrojo
Starting Member
26 Posts |
Posted - 2012-05-30 : 04:13:33
|
| thanks very much NIgelRivett!... I got the result correctly!... more power to YOu and SQLTeam! thanks again! ^_^ |
|
|
|
|
|
|
|
|
Getting the latest date on another table