Contadores y Rangos o funcion Rank() en SQLServer

Crear contadores en SQLSERVER, simple pero útil.

 

1. Contador de registros:

select num = (select count(*) from d_Category r where r.id_Category<=t.id_category), t.* from d_category t

 

1.1. Con la función Ranking (sólo a partir del SQLSERVER 2005):

select rank() over (order by t.id_Category asc) as Ranking, t.* from d_category t order by 1

 

2. Contador por la suma del valor de un campo (por ranking).

En este caso obtendremos los 10 mejores clientes en ventas del 2010

select rank=count(*), s1.Customer, Val=round(s1.Val,0) from (select Customer, Val=round(sum(importe),0) from f_sales where year=2010 group by Customer) s1, (select Customer, Val=round(sum(importe),0) from f_sales where year=2010 group by Customer) s2 where s1.Val<= s2.Val group by s1.Customer having count(*)<11 order by 1

 

2.1. Con la función Ranking (sólo a partir del SQLSERVER 2005):

select * from ( select rank() over (order by importe desc) as Ranking, a.* from (select customer, sum(importe) as importe from f_sales where year=2010 group by Customer ) a ) F_TABLE where Ranking<=10

y filtramos por los 10 primeros, por ejemplo.

 

 

Espero que os ayude.

Héctor Minguet.

 

Up
0