[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

TOP by GROUP SQL 2000

James

3/19/2007 8:30:00 PM

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!
2 Answers

David Portas

3/19/2007 8:45:00 PM

0

On 19 Mar, 20:30, James <J...@discussions.microsoft.com> wrote:
> 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!

Thanks for posting the DDL. It helps if you include KEYS as well.
Without that information I'll guess that the key is (loannumber,
tenant):

SELECT t.loannumber, t.tenant, t.sqft
FROM #tenants AS t
WHERE tenant IN
(SELECT TOP 3 tenant
FROM #tenants
WHERE loannumber = t.loannumber
ORDER BY sqft DESC, tenant)
ORDER BY loannumber, sqft DESC;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Alejandro Mesa

3/19/2007 8:56:00 PM

0

James,

Try using the value from column [tenant] to break ties by [sqft].

SELECT
*
FROM
#tenants AS B1
WHERE
(
SELECT count(*)
FROM #tenants AS B2
WHERE B1.loannumber = B2.loannumber
AND B1.sqft < B2.sqft or (B1.sqft = B2.sqft and B1.tenant < B2.tenant)
) < 3
ORDER BY
loannumber ASC, sqft DESC
go


AMB

"James" wrote:

> 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!