AllanP999 (3/13/2013)
Thankyou for you reply.grps can be updated many times on many dates, so can appear in the table many times.
The table is ordered by date, so I am only interested in the first 'n' rows plus however many rows needed to get all items for the grp.
In this case, its 8 + 5 rows.
So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows
Here's one solution:
with Groups
as (
select top 8 grp
from @tbl
order by dte
)
,GroupsDistinct
as (
select distinct grp
from Groups
)
select t.*
from @tbl t
join GroupsDistinct gd on t.grp = gd.grp
This does, however, bring back ids 16, 17 and 18 because they are in grp 1 (which is selected by the Id 1 row). If these rows should not be selected, you need to modify your description of the problem, as already pointed out by Sergiy.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.