getting top 10 by group

[M]

If I have a query I am writing, I can use the top 10 function to bring
back the top 10 rows. That's all fine if all I want is 10 rows.

What if I have a grouped query, and I have 5 entities that I want 10
rows each from? ie, I have 5 buildings I would want max 50 rows.

I tried Union, which works if you want them all, but I would like the
user to be able to supply me a list of buildings and be able to bring
back 10 rows for each building they ask for.

Possible?

I was thinking of creating a view, or writing a while and looping
through with a query, but I am unsure where to begin.

thanks,
M@

[Plamen Ratchev]

You did not specify by top what but here is a solution that should work. I
simplified and just did the top 4 by the "something" column.

CREATE TABLE #Buildings
(building INT NOT NULL,
something INT NOT NULL)

CREATE TABLE #UserSelectedBuildings
(building INT NOT NULL)

INSERT INTO #Buildings (building, something) VALUES (1, 101)
INSERT INTO #Buildings (building, something) VALUES (1, 102)
INSERT INTO #Buildings (building, something) VALUES (1, 103)
INSERT INTO #Buildings (building, something) VALUES (1, 104)
INSERT INTO #Buildings (building, something) VALUES (1, 105)
INSERT INTO #Buildings (building, something) VALUES (2, 201)
INSERT INTO #Buildings (building, something) VALUES (2, 202)
INSERT INTO #Buildings (building, something) VALUES (2, 203)
INSERT INTO #Buildings (building, something) VALUES (2, 204)
INSERT INTO #Buildings (building, something) VALUES (2, 205)
INSERT INTO #Buildings (building, something) VALUES (3, 301)
INSERT INTO #Buildings (building, something) VALUES (3, 302)
INSERT INTO #Buildings (building, something) VALUES (3, 303)
INSERT INTO #Buildings (building, something) VALUES (3, 304)
INSERT INTO #Buildings (building, something) VALUES (3, 305)

INSERT INTO #UserSelectedBuildings (building) VALUES (1)
INSERT INTO #UserSelectedBuildings (building) VALUES (2)

SELECT DISTINCT B1.building, something
FROM #Buildings AS B1
INNER JOIN #UserSelectedBuildings AS U1 ON B1.building = U1.building
WHERE something <= (SELECT MAX(B2.something)
FROM #Buildings AS B2
WHERE B1.building = B2.building
AND B1.something <= B2.something
HAVING COUNT(DISTINCT something) <= 4)
ORDER BY B1.building ASC, something DESC

DROP TABLE #Buildings
DROP TABLE #UserSelectedBuildings

Regards,

Plamen Ratchev
http://www.SQLStudio.com

"M@" <ma*********@gmail.comwrote in message
news:11**********************@i39g2000hsf.googlegr oups.com...

If I have a query I am writing, I can use the top 10 function to bring
back the top 10 rows. That's all fine if all I want is 10 rows.

What if I have a grouped query, and I have 5 entities that I want 10
rows each from? ie, I have 5 buildings I would want max 50 rows.

I tried Union, which works if you want them all, but I would like the
user to be able to supply me a list of buildings and be able to bring
back 10 rows for each building they ask for.

Possible?

I was thinking of creating a view, or writing a while and looping
through with a query, but I am unsure where to begin.

thanks,
M@

[Erland Sommarskog]

M@ (ma*********@gmail.com) writes:

If I have a query I am writing, I can use the top 10 function to bring
back the top 10 rows. That's all fine if all I want is 10 rows.

What if I have a grouped query, and I have 5 entities that I want 10
rows each from? ie, I have 5 buildings I would want max 50 rows.

I tried Union, which works if you want them all, but I would like the
user to be able to supply me a list of buildings and be able to bring
back 10 rows for each building they ask for.

Possible?

I was thinking of creating a view, or writing a while and looping
through with a query, but I am unsure where to begin.

select building
from (select building, row_number() over(partition by groupid,
order by height desc) AS rn
from buildings) AS b
where rn <= 10

This solution requires SQL 2005. If you are using SQL 2000, your best
bet is probably to insert data into a temp table with an IDENTITY column
and work from that one.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx