select top N distinct

[sagn]

I would like to have returned the top N entries
of a table for each distinct Colvalue s.

For example:
Table

id s
1 w
2 w
3 e
4 w
5 w
6 e
7 e

What I want returned is (N above = 2)
id s
1 w
2 w
3 e
6 e

Any ideas on a query that will do this?

thanks.
(I'm sure I have seen this before and the solution is
probably right under my nose!)

 

[r937]

this is the famous "top N for each X" problem

here is one way to approach it:

select t1.id
     , t1.s
  from daTable as t1
inner
  join daTable as t2
    on t2.s = t1.s
   and t2.id < t1.id
group
    by t1.id
     , t1.s
having count(*) < 2

r937.com | rudy.ca

 

[sagn]

Thanks a lot!

As I said I knew I had seen it before but being
3 pm on a friday my brain is fried!

 

[ESquared]

To my surprise, for very large result sets an ordered insert with a key to a temp table with an identity column can be up to 10 times faster than the derived table method, which is itself up to 10 times faster than a correlated subquery. I think this is because both the derived table and correlated subquery grow as (N*N) whereas the simply order by, while explicitly using tempdb (which we normally want to avoid if possible), is a straight (N).

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[r937]

an ordered insert with an identity column" isn't going to extract the top 2 for each group, is it? becuase that's what was required here

r937.com | rudy.ca

 

[AlexCuse]

If you're on SQL 2005, there is a cool ROW_NUMBER() function that you can use for this.

I'm a fan

Hope it helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[sagn]

I will try those methods as well. The query first listed
is very slow as my table is quite large...

Nonetheless, it did produce the required results and since this is a once and done need I did use it. However, I am curious about the other methods so will check them out as well!

thanks