Need help with a Group By Query 1

[suenram]

I am trying to get the latest time that a table was refreshed in our database and then<br>
print all the information related to that table. The Group by clause keeps us from printing the whole row of data. Could someone help me get the whole row of data after I have found the row I want with a group by clause? I am using SQL Server 7.0 so if there is a way to do this with a stored procedure, please let me know. Thanks.<br>
Karen Suenram<br>
<br>
Select distinct tbl_nm, max(tbl_refresh_ts) <br>
from control_Log<br>
group by tbl_nm <br>
order by tbl_nm<br>
-------------------------------Query Result:<br>
tbl_nm tbl_refresh_ts<br>
Actuals_Detail Jan 5 2000 7:18AM <br>
Budget_Detail Feb 2 2000 7:30AM <br>
<br>
But when I am done I want the information for all 6 columns<br>
Report Needed: <br>
-------- <br>
tbl_nm - tbl_refresh_ts - project_nm - data_source_nm - refresher_id -data_asof_dt <br>
<br>
Actuals_Detail ....... <br>
Budget_Detail.......

 

[yaffle]

How about:<br>
Select tbl_nm, tbl_refresh_ts, project_nm, data_source_nm, refresher_id, data_asof_dt<br>
from control_log<br>
where (tbl_nm, tbl_refresh_ts) in<br>
(Select tbl_nm, max(tbl_refresh_ts) <br>
from control_Log<br>
group by tbl_nm)<br>
order by tbl_nm;<br>

 

[suenram]

I think there was a problem with the comma or having 2 values in 'where (tbl_nm, tbl_refresh_ts)' but you got us on the right track and the following seems to work:<br>
<br>
Select distinct tbl_nm, tbl_refresh_ts,project_nm, data_Source_nm, refresher_id, data_asOf_dt <br>
from control_log<br>
where (tbl_refresh_ts) in <br>
(Select max(tbl_refresh_ts) from control_log<br>
group by tbl_nm) <br>
order by tbl_nm

 

[yaffle]

I plead syntax differences between Oracle & SQL Server - glad you sorted it out