Earliest Date from Common IDs

[TCopple]

I have a tabled which looks something like

Date ID Balance
2007-1-1 1 400000
2006-1-1 1 700000
2005-1-1 1 100000
2007-1-1 2 400000
2006-1-1 2 700000
2005-1-1 2 100000
2007-1-1 3 400000
2006-1-1 3 700000
2005-1-1 3 100000

What I need is a row for each ID which is the earliest date given for that ID. In this case:

2005-1-1 1 100000
2005-1-1 2 100000
2005-1-1 3 100000

Any Suggestions, I'm pretty new to SQL

[almaz]

Please specify the SQL Server version, solutions may be completely different for MSSQL 2000 and MSSQL 2005:
SQL 2005-only solution:

declare @t table (Date datetime, ID int, Balance int, primary key (ID, Date))
insert @t
select Date='2007-1-1', ID=1, Balance=400000
	union all
select '2006-1-1', 1, 700000
	union all
select '2005-1-1', 1, 100000
	union all
select '2007-1-1', 2, 400000
	union all
select '2006-1-1', 2, 700000
	union all
select '2005-1-1', 2, 100000
	union all
select '2007-1-1', 3, 400000
	union all
select '2006-1-1', 3, 700000
	union all
select '2005-1-1', 3, 100000

;with OrderedData(Date, ID, Balance, OrderID)
as
(
	select Date, ID, Balance, row_number() over(partition by ID order by Date)
	from @t
)
select Date, ID, Balance
from OrderedData
where OrderID = 1

Solution that works on both 2000 and 2005 versions:

declare @t table (Date datetime, ID int, Balance int, primary key (ID, Date))
insert @t
select Date='2007-1-1', ID=1, Balance=400000
	union all
select '2006-1-1', 1, 700000
	union all
select '2005-1-1', 1, 100000
	union all
select '2007-1-1', 2, 400000
	union all
select '2006-1-1', 2, 700000
	union all
select '2005-1-1', 2, 100000
	union all
select '2007-1-1', 3, 400000
	union all
select '2006-1-1', 3, 700000
	union all
select '2005-1-1', 3, 100000

select Main.Date, Main.ID, Main.Balance
from @t as Main 
	inner join
	(
		select Date=min(Date), ID
		from @t
		group by ID
	) as MinDate on Main.ID = MinDate.ID and Main.Date = MinDate.Date

[sandyboy]

I have a tabled which looks something like

Date ID Balance
2007-1-1 1 400000
2006-1-1 1 700000
2005-1-1 1 100000
2007-1-1 2 400000
2006-1-1 2 700000
2005-1-1 2 100000
2007-1-1 3 400000
2006-1-1 3 700000
2005-1-1 3 100000

What I need is a row for each ID which is the earliest date given for that ID. In this case:

2005-1-1 1 100000
2005-1-1 2 100000
2005-1-1 3 100000

Any Suggestions, I'm pretty new to SQL

Let the table be T1

SELECT distinct a.date,a.id,a.b alance
FROM T1 as C
JOIN T1 as A
ON C.ID = A.ID
WHERE A.date =
(SELECT min(X.date) FROM T1 as X
WHERE A.ID = X.ID) order by a.ID