what's the right way of joning two tables, group by a column, and select only one row for each record?

I have a crews table

           CREATE TABLE crew(crew_id INT, crew_name nvarchar(20), )

And a time log table, which is just a very long list of actions performed by the crew

  CREATE TABLE [dbo].[TimeLog](
[time_log_id] [int] IDENTITY(1,1) NOT NULL,
[experiment_id] [int] NOT NULL,
[crew_id] [int] NOT NULL,
[starting] [bit] NULL,
[ending] [bit] NULL,
[exception] [nchar](10) NULL,
[sim_time] [time](7) NULL,
    [duration] [int] NULL,
[real_time] [datetime] NOT NULL )

I wa开发者_开发百科nt to have a view that shows only one row for each crew with the latest sim_time + duration .

Is a view the way to go? If yes, how do I write it? If not, what's the best way of doing this?

Thanks

Here is a query to select what you want:

select * from (
    select 
    *,
    row_number() over (partition by c.crew_id order by l.sim_time desc) as rNum
    from crew as c
    inner join TileLog as l (on c.crew_id = l.crew_id)
) as t
where rNum = 1

it depends on what you need that data for.
anyway, a simple query to find latest sim time would be something like

select C.*, TL.sim_time 
from crew C /*left? right? inner?*/ join TimeLog TL on TL.crew_id = C.crew.id
where TL.sim_time in (select max(timelog_subquery.sim_time) from TimeLog timelog_subquery where crew_id = C.crew_id )