Dividing results by percentiles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dividing results by percentiles

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-15 : 12:49:26

I know how to find the top third of something. That's easy. (SELECT TOP (X) PERCENT ...)

How do I nest TOP to find 0-33 percentile and 34-66 percentile results?

Thanks,
Arithmomaniac

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-15 : 16:12:47

perceNTILE function

Peter Larsson
Helsingborg, Sweden

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-16 : 00:39:00

Do I have to write this myself, or is it a UDF?
What is the code for this specific function?
How would I use it to find things in a range, and not jsut the percentile for an object?
Thanks,
Arithmomaniac

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-16 : 01:09:31

50 Percent of 66 percent will give you 33 percent

SELECT TOP 50 percent <col list>
FROM
(
    SELECT TOP 66 percent <col list>
    FROM   TABLE
    ORDER BY somecol
) a
ORDER BY somecol

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 03:07:34

[code]-- Create sample data
DECLARE @Sample TABLE
(
CatID INT,
ParentID INT,
CategoryName VARCHAR(30)
)

INSERT @Sample
SELECT 1, 0, 'Laptops' UNION ALL
SELECT 2, 1, 'Brand New' UNION ALL
SELECT 3, 1, 'Refurbished' UNION ALL
SELECT 4, 1, 'Secondhand' UNION ALL
SELECT 5, 0, 'Desktops' UNION ALL
SELECT 6, 5, 'Brand New' UNION ALL
SELECT 7, 5, 'Refurbished' UNION ALL
SELECT 8, 5, 'Secondhand' UNION ALL
SELECT 9, 5, 'Custom Built' UNION ALL
SELECT 10, 0, 'Laptop Spare Parts' UNION ALL
SELECT 11, 10, 'Hard Drives' UNION ALL
SELECT 12, 11, '1.8' UNION ALL
SELECT 13, 11, '2.5' UNION ALL
SELECT 14, 10, 'Memory RAM' UNION ALL
SELECT 15, 14, 'PC100' UNION ALL
SELECT 16, 14, 'PC133' UNION ALL
SELECT 17, 14, 'DDR' UNION ALL
SELECT 18, 14, 'DDR2' UNION ALL
SELECT 19, 14, 'Other Specific' UNION ALL
SELECT 20, 10, 'AC Adapters' UNION ALL
SELECT 21, 20, 'Toshiba' UNION ALL
SELECT 22, 20, 'Samsung' UNION ALL
SELECT 23, 20, 'ACER'

-- Do the work
SELECT d.CatID,
d.ParentID,
d.CategoryName
FROM (
SELECT CatID,
ParentID,
CategoryName,
NTILE(3) OVER (ORDER BY CatID) AS percentile
FROM @Sample
) AS d
WHERE d.percentile = 2-- 1 for the first third and 3 for the last third[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 03:10:34

quote:

Originally posted by khtan

50 Percent of 66 percent will give you 33 percent

SELECT <col list> FROM (
SELECT TOP 50 percent <col list>
FROM
(
    SELECT TOP 66 percent <col list>
    FROM   TABLE
    ORDER BY somecol
) a
ORDER BY somecol DESC
) b ORDER BY somecol

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-16 : 04:47:11

quote:
Originally posted by Peso

perceNTILE function

Peter Larsson
Helsingborg, Sweden

Good show

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 05:14:57

Yes. One of my better work

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 05:21:46

Feel free to use NTILE() with PARTITION BY statement as well.

NTILE(10) OVER (PARTITION BY ... ORDER BY ...) AS SomeCol
...
WHERE SomeCol = 1

Now you can not only get the TOP 10 records "per group", with NTILE you can get TOP 10 PERCENT "per group" !

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources