I am using sql server 2000 and would like to return only the top 3 values for
each group from the
data below. Is there anyway to do this?

If the square feet is the same, as in loannumber 1, I want to return either
'verizon' or 'home depot', it makes
no difference which one.


CREATE TABLE #tenants
(
loannumber INT NOT NULL,
tenant CHAR(30) NOT NULL,
sqft INT NOT NULL
)

INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (1, 'cosco',101)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (1, 'verizon',102)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (1, 'home depot',102)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (1, 'wendys',104)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (1, 'walmart',105)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (2, 'taco bell',201)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (2, 'blockbuster',202)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (2, 'gap',203)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (2, 'old navy',204)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (2, 'mcdonalds',205)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (3, 'burger king',301)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (3, 'pizza hut',302)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (3, 'outback',303)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (3, 'chase',304)
INSERT INTO #tenants (loannumber, tenant, sqft) VALUES (3, 'starbucks',305)

SELECT DISTINCT B1.loannumber, tenant, sqft
FROM #tenants AS B1
WHERE sqft <= (SELECT MAX(B2.sqft)
FROM #tenants AS B2
WHERE B1.loannumber = B2.loannumber
AND B1.sqft <= B2.sqft
HAVING COUNT(DISTINCT sqft) <= 3)
ORDER BY B1.loannumber ASC, sqft DESC

DROP TABLE #tenants

Thanks!

Clustered Index Not Working
Select distinct problems
How to compare two tables of same structure using a hash/checksum
Newbie question for Report Server
strange type conversion error with datetime...