Simple Count Query Help

[ncotton]

I have a list of events.
i.e

A | B
-----
1 | X
2 | Y
3 | X
4 | Z
5 | Z
6 | Z

I need a to structure a query so that I can see the nth most accurance in the B column.

So 2nd most occuring letter in B.

Any help?

Hope this Helps.

Neil J Cotton
njc Information Systems
Systems Consultant

 

[Welshbird]

Hi Neil!

I'd use this as a temp thing:

create table occurances nologging as
select
a, 
b,        
row_number() over (partition by B order by A) as OCC
from Neil_Table

Then the OCC column should show you what you need.

Let me know if this isn't what you mean!



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ncotton]

Fee, can I email you at your WB.com email?

Hope this Helps.

Neil J Cotton
njc Information Systems
Systems Consultant

 

[Welshbird]

Of course.

Or use the same email address on MSN if that helps.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

select thing, thang, rank from
(
select thing, thang, rank() over (order by thang desc) as rank
from
(
select thing, count(thing) as THANG
from neil
group by thing
))
where rank = 2


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Neil - after realising that this will give you odd results if you have tied scores, I suggest the following:

select thing, thang, rank from
(
select thing, thang, dense_rank() over (order by thang desc) as rank
from
(
select thing, count(thing) as THANG
from neil
group by thing
))
where rank = 2

Hopefully that will work for you.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]