quote:

---

Originally posted by Erik.doe

I need to build a query that compares the date field from two tables.
The result only need to be table1.id.
In the result must the table1.id only be once

---

SET dateformat dmy
go

DECLARE @Table1 TABLE (id INT, Date DATETIME)
INSERT INTO @Table1
SELECT 1, '2-11-2007' UNION ALL
SELECT 2, '4-11-2007' UNION ALL
SELECT 3, '6-11-2007' UNION ALL
SELECT 4, '8-11-2007' UNION ALL
SELECT 5, '10-11-2007' UNION ALL
SELECT 6, '15-11-2007'

DECLARE @Table2 TABLE (id INT, table1_id INT, Date DATETIME)
INSERT INTO @Table2
SELECT 1, 1, '3-11-2007' UNION ALL
SELECT 2, 1, '7-11-2007' UNION ALL
SELECT 3, 1, '9-11-2007' UNION ALL
SELECT 4, 3, '7-11-2007' UNION ALL
SELECT 5, 3, '12-11-2007' UNION ALL
SELECT 6, 5, '11-11-2007'

SELECT T1.Id, Coalesce(T2.T2Date, T1.date)
FROM @Table1 T1
LEFT JOIN ( SELECT table1_id, Max(Date) T2Date
		FROM @Table2 
		GROUP BY table1_id
	) T2 ON T1.Id = T2.table1_id

	

SELECT 
	T1.Id
FROM 
	@Table1 AS T1
LEFT OUTER JOIN
	(
		SELECT table1_id, MAX(Date) AS Date
		FROM @Table2 
		GROUP BY table1_id
	) AS T2
	ON T1.ID = T2.table1_ID
ORDER BY
	CASE
		WHEN T2.Date IS NULL THEN T1.Date
		WHEN T1.Date > T2.Date THEN T1.Date
		ELSE T2.Date
	END DESC