group by + rowcount

I have a simple query with group by.
and I want to get the topX rows from each group.
how to do that ?
for example
declare @.myTable table ( id int identity(1,1),
comment int,
someDate datetime
)
select comment, someDate
from @.myTale
group by commentNoam
declare @.myTable table ( id int identity(1,1),
comment int,
someDate datetime
)
insert into @.myTable (comment,someDate)values (1,'20050101')
insert into @.myTable (comment,someDate)values (1,'20050102')
insert into @.myTable (comment,someDate)values (1,'20050103')
insert into @.myTable (comment,someDate)values (2,'20040101')
insert into @.myTable (comment,someDate)values (2,'20040102')
insert into @.myTable (comment,someDate)values (2,'20040103')
select comment, someDate from @.myTable t
where somedate in(select top 2 somedate from @.myTable a where
a.comment=t.comment
order by somedate desc)
"Noam" <anonymous@.discussions.microsoft.com> wrote in message
news:182c01c50f7a$196e4150$a601280a@.phx.gbl...
> I have a simple query with group by.
> and I want to get the topX rows from each group.
> how to do that ?
> for example
> declare @.myTable table ( id int identity(1,1),
> comment int,
> someDate datetime
> )
> select comment, someDate
> from @.myTale
> group by comment
>|||Then do not use group by.
Example:
use northwind
go
declare @.i int
set @.i = 3
select
c.country,
oh.orderid
from
orders as oh
inner join
customers as c
on oh.customerid = c.customerid
where
(select count(*) from orders as a inner join customers as b on a.customerid
= b.customerid where b.country = c.country and a.orderid >= oh.orderid) <= @.
i
order by
c.country,
oh.orderid
go
AMB
"Noam" wrote:

> I have a simple query with group by.
> and I want to get the topX rows from each group.
> how to do that ?
> for example
> declare @.myTable table ( id int identity(1,1),
> comment int,
> someDate datetime
> )
> select comment, someDate
> from @.myTale
> group by comment
>|||Hi Noam,
Hope By now you have got the solution.
but I am afraid your query for the original groupby does not appear to be
syntatically correct. you should have samecolumns in the group by as well as
Select List in your case.
Jai
"Noam" wrote:

> I have a simple query with group by.
> and I want to get the topX rows from each group.
> how to do that ?
> for example
> declare @.myTable table ( id int identity(1,1),
> comment int,
> someDate datetime
> )
> select comment, someDate
> from @.myTale
> group by comment
>