Ranking ?

  • Thread starter Thread starter Paulo
  • Start date Start date
P

Paulo

Hi, I need to create a ranking column wich will be the row number... it is
the seller wich most sells...

Ranking Seller Sum
1 Paul 2.212,00
2 Robert 1.500,00
3 ....
.....

I have already the sql, but I dont know how to do the ranking number
column...

Can you help me?

Using VS 2005 and Sql Server 2000

Thanks !
 
Your question is not clear. U say you have the sql so what is it you are
missing ?

Post the SQL you have and let us know what it is you need specifically
 
strSQL = "Select Empresa.NOME_EMPRESA,Vendedor.NOME_VENDEDOR
NOME_PROMOTOR,Sum(CONTRATO.VL_CREDITO) VL_CREDITO";
strSQL += " From Contrato,Empresa,Vendedor Where
CONTRATO.COD_EMPRESA=EMPRESA.COD_EMPRESA And ";
strSQL += "Contrato.COD_VENDEDOR=VENDEDOR.ID_VENDEDOR Group By
NOME_EMPRESA,NOME_VENDEDOR ";
strSQL += "Order by VL_CREDITO Desc";

Sorry, I forgot...

Is there any way creating on the sql the column ranking with the number?

Thanks
 
You can put the result from your select statement into a temp #table. your
temp table can have an Id column (ranknum) that is an auto increment to rank
your rows. or do it in code behind.

change the type I put nvarchar(100) Null on all

DECLARE @Empresa TABLE (
ranknum int IDENTITY(1,1) NOT NULL PRIMARY KEY,
NOME_EMPRESA nvarchar(100) NULL,
VendedorNOME_VENDEDOR nvarchar(100) NULL,
NOME_PROMOTORnvarchar(100) NULL,
sumVL_CREDITO nvarchar(100) NULL
)

then populate the temp table with the rank

INSERT into @Empresa (NOME_EMPRESA,VendedorNOME_VENDEDOR
,NOME_PROMOTORnvarchar,sumVL_CREDITO)
Select Empresa.NOME_EMPRESA,Vendedor.NOME_VENDEDOR
NOME_PROMOTOR,Sum(CONTRATO.VL_CREDITO) VL_CREDITO
From Contrato,Empresa,Vendedor Where
CONTRATO.COD_EMPRESA=EMPRESA.COD_EMPRESA And
Contrato.COD_VENDEDOR=VENDEDOR.ID_VENDEDOR Group By
NOME_EMPRESA,NOME_VENDEDOR
Order by VL_CREDITO Desc


Finally

select * from @Empresa
 
I reckon there are many ways to do this
Patrick

IfThenElse said:
You can put the result from your select statement into a temp #table.
your temp table can have an Id column (ranknum) that is an auto increment
to rank your rows. or do it in code behind.

change the type I put nvarchar(100) Null on all

DECLARE @Empresa TABLE (
ranknum int IDENTITY(1,1) NOT NULL PRIMARY KEY,
NOME_EMPRESA nvarchar(100) NULL,
VendedorNOME_VENDEDOR nvarchar(100) NULL,
NOME_PROMOTORnvarchar(100) NULL,
sumVL_CREDITO nvarchar(100) NULL
)

then populate the temp table with the rank

INSERT into @Empresa (NOME_EMPRESA,VendedorNOME_VENDEDOR
,NOME_PROMOTORnvarchar,sumVL_CREDITO)
Select Empresa.NOME_EMPRESA,Vendedor.NOME_VENDEDOR
NOME_PROMOTOR,Sum(CONTRATO.VL_CREDITO) VL_CREDITO
From Contrato,Empresa,Vendedor Where
CONTRATO.COD_EMPRESA=EMPRESA.COD_EMPRESA And
Contrato.COD_VENDEDOR=VENDEDOR.ID_VENDEDOR Group By
NOME_EMPRESA,NOME_VENDEDOR
Order by VL_CREDITO Desc


Finally

select * from @Empresa
Click to expand...
 
Back
Top