Multiple "Top 100" datasets 1

[oharab]

I have a table of data:

tData:
ID,Division,Some,Stuff,Here

where ID is unique & the rest isn't.

I also have a table of just

tCriteria:
Division

where Division is Unique

What I'd like is for each Division in tCriteria, return the top 100 rows from tData where tCriteria.Division=tCriteria.Division so if there are 5 records in tCriteria, I will get 500 records.
I'm using SQL Server 2005 and know I can use cursors, but I'm sure there is a set based way.

Any thoughts?

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[PHV]

One way:
SELECT A.ID,A.Division,A.Some,A.Stuff,A.Here
FROM tData A
INNER JOIN tData B ON A.Division=B.Division AND A.ID<=B.ID
INNER JOIN tCriteria C ON A.Division=C.Division
GROUP BY A.ID,A.Division,A.Some,A.Stuff,A.Here
HAVING COUNT(*)<=100

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AlexCuse]

This assumes you want the top 100 ordered by ID ascending.

[COLOR=blue]select[/color] [COLOR=blue]d[/color].*
[COLOR=blue]from[/color] tData [COLOR=blue]d[/color]
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] tData [COLOR=blue]t[/color]
[COLOR=blue]on[/color] [COLOR=blue]d[/color].Division = [COLOR=blue]t[/color].Division
and [COLOR=blue]d[/color].ID <= [COLOR=blue]t[/color].ID
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] tCriteria c
[COLOR=blue]on[/color] [COLOR=blue]d[/color].Division = c.Division
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=blue]d[/color].ID, [COLOR=blue]d[/color].Division, [COLOR=blue]d[/color].[Some], [COLOR=blue]d[/color].[[COLOR=#FF00FF]Stuff[/color]], [COLOR=blue]d[/color].Here
[COLOR=blue]having[/color] [COLOR=#FF00FF]count[/color]([COLOR=blue]t[/color].ID) <= 100

Hope it helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Whoops there is mistake in my query (both joins should be inner). So use PH's instead

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[oharab]

Once again, PHV to the rescue. Many thanks, just what was required.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.