sql server programming: Top Few Results for each row in a one-to-many result set -- sql server programming

Top Few Results for each row in a one-to-many result set

EradicusMax
11/12/2006 11:21:26 PM

sql server programming: I'm sure someone has had to do this before for a batch process. Is there a
way to return the first few records for each row of a result set. Lets say I
have a table (Accounts) with

AcctNbr, LastName,FirstName,State

I have a second table (Phones) for the phone listings of the the US with

LastName, FirstName, State, PhoneNumber.

Lets say I'm querying for lastname, firstname, and state

Accounts.LastName=Phones.LastName AND Accounts.FirstName=Phones.FirstName
AND Accounts.State=Phones.State

This brings back a large 1-to-many result set, especially say a John Smith.

Is there a simple way to get, say, the first 3 listings for each AcctNbr,
even though there may be hundreds?

TIA

Re: Top Few Results for each row in a one-to-many result set

Uri Dimant
11/13/2006 12:00:00 AM

Hi
create table EradicusMax(
F1 char(1),
F2 int
)

insert into EradicusMax values('A',10)
insert into EradicusMax values('A',8)
insert into EradicusMax values('A',7)
insert into EradicusMax values('B',12)
insert into EradicusMax values('B',8)
insert into EradicusMax values('B',6)
insert into EradicusMax values('C',1)

SELECT * FROM EradicusMax E
WHERE (
SELECT COUNT(*) FROM EradicusMax
WHERE F1 = E.F1
AND F2 > E.F2
) <= 1
ORDER BY F1, F2 DESC

[quoted text, click to view]