Limiting the amount of rows

I want to list a particular attribute value and the number of times it occurs
in the database ie
Attribute value 1 6
Attribute value 2 5
Attribute value 3 5
Attribute value 4 4
Attribute value 5 4
Attribute value 6 3
Attribute value 7 3
Attribute value 8 3
Attribute value 9 3
Attribute value 10 2
Attribute value 11 2
Attribute value 12 1
Attribute value 13 1
However I want the result set to list the top 10 records. This could be
done using the top 10 function. However as you can see Attribute value
10 and 11 both have the total of 2 and are therefore equilvalent. So I
really want the top 11 returned in this case. If Attribute value 12
had been 2 then I would have wanted the top 12 etc
How can I do this?MTKIRWAN wrote:
> I want to list a particular attribute value and the number of times
> it occurs in the database ie
> Attribute value 1 6
> Attribute value 2 5
> Attribute value 3 5
> Attribute value 4 4
> Attribute value 5 4
> Attribute value 6 3
> Attribute value 7 3
> Attribute value 8 3
> Attribute value 9 3
> Attribute value 10 2
> Attribute value 11 2
> Attribute value 12 1
> Attribute value 13 1
> However I want the result set to list the top 10 records. This could
> be done using the top 10 function. However as you can see Attribute
> value 10 and 11 both have the total of 2 and are therefore
> equilvalent. So I really want the top 11 returned in this case. If
> Attribute value 12
> had been 2 then I would have wanted the top 12 etc
> How can I do this?
Try something like this:
In this example, I'm looking for the TOP 4. There are 6 groups. If you
run a GROUP BY with COUNT in DESC order on the COUNT, you'll see we have
a matching count on 4 and 5, so what we really want is the first five
groups.
What I did was use an IN query to pull in just the raw totals for the
highest 4 rows and then use those results in the HAVING clause. The main
query does not use a TOP clause because we are limited by the HAVING.
create table #test (Attr varchar(10))
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr2')
insert into #test values ('Attr2')
insert into #test values ('Attr2')
insert into #test values ('Attr3')
insert into #test values ('Attr3')
insert into #test values ('Attr3')
insert into #test values ('Attr4')
insert into #test values ('Attr4')
insert into #test values ('Attr5')
insert into #test values ('Attr5')
select Attr, COUNT(*) as 'Count'
from #test
Group By Attr
Having COUNT(*) IN (
select TOP 4 COUNT(*) as 'Count'
from #test
Group By Attr
Order By 1 Desc)
Order By 2 Desc
David Gugick
Imceda Software
www.imceda.com|||On Wed, 22 Sep 2004 18:59:03 -0700, MTKIRWAN wrote:
> However I want the result set to list the top 10 records. This could be
> done using the top 10 function. However as you can see Attribute value
>10 and 11 both have the total of 2 and are therefore equilvalent. So I
> really want the top 11 returned in this case. If Attribute value 12
>had been 2 then I would have wanted the top 12 etc
> How can I do this?
TOP 10 WITH TIES
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Wed, 22 Sep 2004 18:59:03 -0700, MTKIRWAN wrote:
>> However I want the result set to list the top 10 records. This could
>> be done using the top 10 function. However as you can see Attribute
>> value 10 and 11 both have the total of 2 and are therefore
>> equilvalent. So I really want the top 11 returned in this case. If
>> Attribute value 12 had been 2 then I would have wanted the top 12 etc
>> How can I do this?
> TOP 10 WITH TIES
> ?
> Best, Hugo
I guess you learn something every day. Never knew.
--
David Gugick
Imceda Software
www.imceda.com