Selecting one row for each id

[SQLJoe]

I need to be able to select only one row out of a table for each donor id present, even when the donor id is listed multiple times (once for each donation).

For example, my data looks like this:

donor1 fund_a amt_a
donor1 fund_b amt_b
donor1 fund_c amt_c
donor2 fund_a amt_a
donor3 fund_c amt_c
donor3 fund_d amt_d

I need my query results to look like this:

donor1 fund_a amt_a
donor2 fund_a amt_a
donor3 fund_c amt_c

(It actually doesn't matter which fund/amt are listed, so long as each donor is listed only once.)

I'm unclear on how to accomplish this. I'm sure there's an easy way, but I've never had a business need like this.

 

[markros]

The simplest way will be (SQL 2005+):

;with cte as (select *, row_number() over (partition by Donor
order by Fund, Amt) as Row from DonorsInfo)

select * from cte where Row = 1

PluralSight Learning Library

 

[SQLJoe]

Works great, thanks!