create table dbo.tbl1 ([ID] int IDENTITY(1,1) PRIMARY KEY CLUSTERED, Field1 varchar(10)
	, Field2 varchar(10))

create table dbo.tbl2 ([ID] int IDENTITY(1,1) PRIMARY KEY CLUSTERED, Table1ID int
	, [TimeStamp] timestamp, FieldX varchar(10), FieldY varchar(10))


insert into tbl1 (Field1,Field2) values ('foo','Bar') 
insert into tbl1 (Field1,Field2) values ('blah','up') 
insert into tbl1 (Field1,Field2) values ('say','snoogie') 
insert into tbl1 (Field1,Field2) values ('what','yes') 
insert into tbl1 (Field1,Field2) values ('tool','no') 


insert into tbl2 (Table1ID,FieldX,FieldY) values (1,'Yo','Gah')
insert into tbl2 (Table1ID,FieldX,FieldY) values (1,'Blea','nah')
insert into tbl2 (Table1ID,FieldX,FieldY) values (1,'Last','Insert')
insert into tbl2 (Table1ID,FieldX,FieldY) values (2,'Yo','Gah')
insert into tbl2 (Table1ID,FieldX,FieldY) values (2,'Last','Insert')
insert into tbl2 (Table1ID,FieldX,FieldY) values (3,'Yo','Gah')
insert into tbl2 (Table1ID,FieldX,FieldY) values (3,'Yo','Gah')
insert into tbl2 (Table1ID,FieldX,FieldY) values (3,'Last','Insert')

Select  tbl1.Field1, tbl1.Field2, tbl1.ID, tbl2.FieldX, tbl2.FieldY , tbl2.[TimeStamp]
From tbl1 inner join tbl2 
on tbl1.ID = tbl2.Table1ID
where tbl2.[TimeStamp] in 
(select [TimeStamp] from
	( select table1id, max([TimeStamp]) AS [TimeStamp] from tbl2 group by table1id) G
) 

drop table tbl1
drop table tbl2

quote:

---

Originally posted by DonAtWork

1) habit
2) just trying to construct the tables from how the question asker described them.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

---

SELECT  
	tbl1.Field1, 
	tbl1.Field2, 
	tbl1.ID, 
	tbl2.FieldX, 
	tbl2.FieldY , 
	tbl2.[TimeStamp]
FROM 
	tbl1 
INNER JOIN 
	tbl2 
	ON tbl1.ID = tbl2.Table1ID
INNER JOIN
	(
		SELECT 
			table1id, 
			MAX([TimeStamp]) AS [TimeStamp] 
		FROM 
			tbl2 
		GROUP BY 
			table1id
	) AS Temp
	ON tbl2.Table1ID = Temp.Table1ID
	AND tbl2.TimeStamp = Temp.TimeStamp

SELECT  
	tbl1.Field1, 
	tbl1.Field2, 
	tbl1.ID, 
	tbl2.FieldX, 
	tbl2.FieldY , 
	tbl2.[TimeStamp]
FROM 
	tbl1 
INNER JOIN 
	(
		SELECT 
			table1Id, 
			MAX([TimeStamp]) AS [TimeStamp] 
		FROM 
			tbl2 
		GROUP BY 
			table1id
	) AS tbl2
	ON tbl1.ID = tbl2.Table1ID

quote:

---

Originally posted by arajderkar

SELECT  
	tbl1.Field1, 
	tbl1.Field2, 
	tbl1.ID, 
	tbl2.FieldX, 
	tbl2.FieldY , 
	tbl2.[TimeStamp]
FROM 
	tbl1 
INNER JOIN 
	(
		SELECT 
			table1Id, 
			MAX([TimeStamp]) AS [TimeStamp] 
		FROM 
			tbl2 
		GROUP BY 
			table1id
	) AS tbl2
	ON tbl1.ID = tbl2.Table1ID

---

-- Using just pk2
SELECT
	T.pk1,
	T.pk2,
	T.Start,
	T.f1,
	T.f2
FROM 
	@T AS T
INNER JOIN
	(
		SELECT pk1, MAX(pk2) AS pk2
		FROM @T AS T1
		GROUP BY pk1
	) AS Temp
	ON T.pk1 = Temp.pk1
	AND T.pk2 = Temp.pk2


--Using Start then MAX
SELECT
	T.pk1,
	MAX(T.pk2) AS pk2,
	MAX(T.Start) AS Start,
	MAX(T.f1) AS f1,
	MAX(T.f2) AS f2
FROM 
	@T AS T
INNER JOIN
	(
		SELECT pk1, MAX(Start) AS Start
		FROM @T AS T1
		GROUP BY pk1
	) AS Temp
	ON T.pk1 = Temp.pk1
	AND T.Start = Temp.Start
GROUP BY
	T.pk1

-- For Grins - The 2005 Way - Using Start then pk2
SELECT
	Temp.pk1,
	Temp.pk2,
	Temp.Start,
	Temp.f1,
	Temp.f2
FROM
	(
		SELECT
			T.pk1,
			T.pk2,
			T.Start,
			T.f1,
			T.f2,
			ROW_NUMBER() OVER (PARTITION BY t.pk1 ORDER BY t.Start DESC, t.pk2 DESC) AS RowNum
		FROM 
			@T AS T
	) AS Temp
WHERE RowNum = 1

-- prepare sample data
create table #t (pk1 int, pk2 int, start datetime, f1 char(10), f2 char(10))

set dateformat dmy

insert	#t
select	1, 1, '01.01.2007', 'f11', 'f21' union all
select	1, 2, '02.01.2007', 'f12', 'f22' union all
select	1, 3, '03.01.2007', 'f13', 'f23' union all
select	2, 1, '04.01.2007', 'f14', 'f24' union all
select	2, 2, '05.01.2007', 'f15', 'f25' union all
select	2, 3, '06.01.2007', 'f16', 'f26' union all
select	3, 1, '07.01.2007', 'f17', 'f27' union all
select	3, 2, '09.01.2007', 'f18', 'f28' union all
select	3, 3, '09.01.2007', 'f19', 'f29'

-- Reporter / 65% of batch / duration 1, reads 15
select		v2.*
from		(
			select		v1.pk1,
					pk2 = max(v2.pk2),
					v1.start
			from		(
						select		pk1,
								start = max(start)
						from		#t
						group by	pk1
					) as v1
			inner join	#t v2 on v1.pk1 = v2.pk1 and v1.start = v2.start
			group by	v1.pk1,
					v1.start
		) as v1
inner join	#t v2 on v1.pk1 = v2.pk1 and v1.pk2 = v2.pk2

-- Peso / 11% of batch / duration 0, reads 30
select	t1.pk1,
	t1.pk2,
	t1.start,
	t1.f1,
	t1.f2
from	#t as t1
where	exists (select * from #t as t2 where t2.pk1 = t1.pk1 group by t2.pk1 having convert(varchar, t1.start, 120) + cast(t1.pk2 as varchar) = max(convert(varchar, t2.start, 120) + cast(t2.pk2 as varchar)))

-- SQL Server 2005 / 24% of batch / duration 0, reads 3
select	pk1,
	pk2,
	start,
	f1,
	f2
from	(
		select	pk1,
			pk2,
			start,
			f1,
			f2,
			row_number() over (partition by pk1 order by start desc, pk2 desc) as recid
		from	#t
	) as d
where	recid = 1

-- clean up
drop table #t

-- Reporter
-- Scan count 3, logical reads 5, physical reads 0
--
-- Peso
-- Scan count 10, logical reads 10, physical reads 0
--
-- SQL Server 2005
-- Scan count 1, logical reads 1, physical reads 0