top 10 biggest values? 1

[Fursten]

Hi,

I have a select with a count and a group by.
I need do filter the rows returned to those that have the top 10 biggeste values in the count column. Top 10 only gives the 10 first returned records, and that is not what i need.

thank you

 

[checkai]

top 10

did you put

order by count(*) desc

 

[donutman]

What do you want to return if there are 12 groups that all have the save count which happens to be the highest count of all groups?
Maybe you want all the groups that have the highest 10 distinct counts.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Fursten]

Hi,

Checkai, your solution works, despite I get all the records.
I know there is a method of return the first n records based on the value of one column. It is not top most, but I don´t remember.

Thank you

 

[SQLBill]

Can you post your query? Checkai's solution should be returning ONLY 10 rows based on the COUNT(*).

Ordering the results by the COUNT(*) DESC will put the returned rows in the order of highest count to lowest count and then return only the TOP 10 of those.

-SQLBill

Posting advice: FAQ481-4875

 

[r937]

fursten wanted the top 10 values, not the top 10 rows

this is untested --

select foo
     , count(*) 
  from yourtable
group by foo
having count(*)
    in ( select top 10 distinct count(*) 
           from yourtable
         group by foo 
         order by count(*) desc ) as dt
order by count(*) desc

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SQLBill]

The TOP 10 values is the same as TOP 10 rows. If you want ties you just use the WITH TIES option.

Ex:
Values
1
5
7
8
2
6
3
5
6
9

SELECT TOP 5 Values
FROM mytable
ORDER BY Values DESC

Values
9
8
7
6
6

SELECT TOP 5 WITH TIES Values
FROM mytable
ORDER BY Values DESC

Values
9
8
7
6
6
5
5

I use TOP x daily with COUNT(myfield) and it works just fine for giving me the TOP x values.

-SQLBill

Posting advice: FAQ481-4875

 

[donutman]

I wasn't aware of a "With Ties" construct." Thanks.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

with ties" only works correctly if the ties happen to go across the Nth place

in general, the top N values is not the same as the top N rows, with or without ties

here are 15 rows --

55
50
50
50
50
44
40
40
40
33
30
30
22
20
11

the top 5 values are: 55,50,44,40,33

the top 5 rows are not the same


yes, it's "semantics"

yes, it's splitting hairs

but i think that's what fursten wanted

read the first post again




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SQLBill]

Rudy,

Could be, but this:

filter the rows returned to those that have the top 10 biggeste values in the count column

appears to me to mean the TOP 10 rows with the largest/biggest values in the COUNT(*) column using the ORDER BY COUNT(*) DESC.

But without the posters input or examples, we can only guess. SET ROWCOUNT 10 might be what the poster is looking for.

-SQLBill

Posting advice: FAQ481-4875

 

[r937]

yes, clarification would be nice

if you ask "what are the top 3 student marks" when there are seven with 80%, one with 79%, one with 78%, and four with 77%, then with or without ties, the TOP method returns either 3 or 7

however, "which students got the top 3 marks" would return 9 of them





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SQLBill]

So Fursten, we've given you suggestions, examples and our thoughts. Now let us know if we helped you out (and what did help) or give us an example of what you are looking for.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

The original poster never said anything about Count(*). He or she said "the count column." Everyone's been interpreting that as count(*). Are you sure that's what was intended? Could it be as simple as:

SELECT TOP 10
  Blah
FROM
  Blah
ORDER BY
  [Count] DESC

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[donutman]

If you ignore sentence 1 and 3 that could be a reasonable interpretation, but let's see how often we can shoot in the dark at a moving target without night vision goggles.
Maybe, he went on vacation.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Oops! Where's the egg-on-face emoticon?

 

[r937]

yeah, line 1 of the first post actually says GROUP BY

)



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

But the message at least doesn't say count(*) so I was halfway right.

 

[donutman]

Hey, rudy, let's keep bumping this thread up to the top, so that it might look like ESquared makes as many mistakes as I know I do.
BTW, e[sup]2[/sup] I think this is the one you were looking for.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

good idea

hey, isn't that the meep-meep road runner?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

I make mistakes all the time.