Custom Search : Technical Guides Sybase Oracle UNIX Javascript

Technical Guides

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

create table #rank99 (
rec_id numeric(8) identity,
salesman_id integer not null,
gross_sales_amt real not null

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

Sybase : Related Topics

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site

Get the latest Rocket99 news and tech tips via

Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site

Copyright © 2016 Stoltenbar Inc All Rights Reserved.