Selecting the Earliest ID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting the Earliest ID

Author

Topic

IainT
Starting Member

12 Posts

Posted - 2007-04-20 : 05:40:40

Hey guys

I'm extremely puzzled by some SQL and in need of some help.

The table set up is very simple - I have just one 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
9..........1/1/2005..........2

It should return:

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

or the following would also be fine:

giftID....constituentID
9...........2
5...........3

It should not return:

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

Any help would be gratefully received! Any ideas?

Cheers
Iain

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 06:22:13

Why not both 1 and 9? How SQL is going to select either of one when dates for both are same?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-20 : 06:25:32

[code]SELECT GiftID,
GiftDate,
ConstituentID
FROM (
SELECT GiftID,
GiftDate,
ConstituentID,
ROW_NUMBER() OVER (PARTITION BY ConstituentID ORDER BY GiftDate) AS RecID
FROM Table1
) AS d
WHERE RecID = 1[/code]

Peter Larsson
Helsingborg, Sweden

IainT
Starting Member

12 Posts

Posted - 2007-04-20 : 06:25:34

harsh_athalye: Because I only want one gift per constituent, but if both are on the same date then it doesn't matter which one is selected. It's an unusual situation.

Peso: Thanks. I'll have a look at that and get back to you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources