September 22, 2004 at 8:45 pm
Hi,
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 etcHow can I do this?
Regards,
Matthew
September 22, 2004 at 9:50 pm
I think the way to do this is to find the top 10 'counts' and then to return all the records that have these counts.
So, if your table is called 'test' and the occurrences column is called 'count':
select * from test where count in (select distinct top 10 count from test order by count desc)
Should do the trick.
Regards
Phil
September 23, 2004 at 12:21 am
for example
select top 10 * from costomer ......
bye
September 23, 2004 at 12:28 am
If you re-read the original post, you will see why the straightforward 'top 10' method will not work in this case.
September 23, 2004 at 1:54 am
SELECT TOP 10 WITH TIES should do the trick
September 23, 2004 at 5:38 pm
Excellent, never used that before. Far better than my suggestion.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply