Of Interest
Business Intelligence and Analytics
Oracle Training
|
Sybase
»
T-SQL
»
Analytic
»
NTile
N-Tile Analytic Queries
Analytic Functions: N-Tile
N-tile type queries allow you to ask for results based on which ranking group they fall into.
For example (5 parts, quintile):
"Show me all records where they fall in the top fifth of the set, ranked by gross sales."
There is no n-tile function in ASE (there is in IQ) ... here is how to do set up the query using
Transact-SQL.
create table #rank99 (
rec_id numeric(8) identity,
salesman_id integer not null,
gross_sales_amt real not null
)
go
declare @piece real
insert into #rank99 ( gross_sales_amt, salesman_id )
select gross_sales_amt, salesman_id
from regional_sales
where year_month_id = 200510
and isnull(gross_sales_amt,0) > 0
order by 1
select @piece = @@rowcount/5.0
select ceiling(rec_id/@piece) 'Quin-Tile', salesman_id, gross_sales_amt
from #rank99
order by 1,2,3
go
|
|
|
Get the latest Rocket99 news and tech tips via
|
