Simple SQL Problem

[IainT]

Hey guys

Sorry to charge in and ask a question with my first post, but I'm extremely puzzled by some SQL and really in need of some help.

The table set up is very simple - I have just one relevant table called Gift. It has three relevant fields - giftID, giftDate and constituentID. I need to find the earliest giftID for each constituentID. Simple, ya?

So if the data was

giftID...giftDate...constituentID
3..........1/1/2006..........2
1..........1/1/2005..........2
5..........2/2/2006..........3
5..........2/2/2006..........2
8..........8/8/2006..........3

It should return

giftID....constituentID
1...........2
5...........3

I can't do this though without using several very slow subqueries which I'm finding quite annoying, because I'm sure it should be possible in one simple SQL statement.

Any help would be gratefully received! Any ideas?

Cheers
Iain

[russell]

What dbms are u using? this will work for most.

SELECT	t.giftID, t.constituentID
FROM	tableName t
JOIN	(
	SELECT	constituentID, min(giftDate) dt
	GROUP BY
		constituentID
) x
On	x.constituentID = t.constituentID
And	x.dt = t.giftDate

[IainT]

Cheers Russell. Not too different, but still much more efficient apparently than the SQL I was using.

Much appreciated.
I

[IainT]

Cheers Russell. Not that different to the SQL I had been using, but apparently much more efficient.

Much appreciated!
I