harvey chaplin

I have a query that returns data values ordered by Asc
where the data is between 2 dates. Can I select The TOP 3
items in each group? I think that the TOP command selects
all data in a single field irrespective of grouping.

vishal parkar

Harvey,

Without table structure/sample data it won't be possible to give you correct solution. On
the basis of guesswork I've created some sample data and the query that may satisfy your
requirement.

--table
create table #t
(grp varchar(50), itemid int, groupname varchar(50))

--sample records.
insert into #t
select 'group1', 1 , 'groupname 1' union all
select 'group1', 2 , 'groupname 1' union all
select 'group1', 3 , 'groupname 1' union all
select 'group1', 4 , 'groupname 1' union all
select 'group1', 5 , 'groupname 1' union all
select 'group2', 1 , 'groupname 2' union all
select 'group2', 2 , 'groupname 2' union all
select 'group2', 3 , 'groupname 2' union all
select 'group2', 4 , 'groupname 2' union all
select 'group2', 5 , 'groupname 2' union all
select 'group2', 6 , 'groupname 2' union all
select 'group3', 1 , 'groupname 3' union all
select 'group3', 2 , 'groupname 3'

--Required query to get top 3 itemid's for each group.

select grp,itemid,groupname from
(select a.grp,a.itemid,a.groupname,
(select count(distinct itemid) from #t b
where a.grp = b.grp
and a.itemid <= b.itemid ) rank --condition (i)
from #t a ) X
where rank <= 3
order by grp, rank

The above query will rank the itemid's in descending order to make this order ascending
change the above querie's "condition (i)" as follows

and b.itemid <= a.itemid ) rank

--
-Vishal