Posted by Plamen Ratchev on 01/10/07 23:14

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@" <mattcushing@gmail.com> wrote in message
news:1168456139.077774.278220@i39g2000hsf.googlegroups.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@
>

[Back to original message]