top n percentage

[andrew]

Hello!

I have a need to create a function that will return a sublist of
elements from a table based on an associated column value and its
ranking within the table (based on a percentage range).

To expand with a simple example (only 4 elements):

Element | Value
---------------
A102 | 5
A506 | 10
A322 | 15
A342 | 20

I would like to be able to return the range of elements that lie in the
50-75% value range (e.g. third quartile range). Therefore with only
these four elements I would want to return the element code 'A322'.

I have attempted to achieve this by using SELECT TOP n PERCENT. I can
bring back the required range by combining two select percent
statements.

SELECT Element from ELEMENT_TABLE
WHERE Element IN
(SELECT TOP 75 PERCENT Element FROM ELEMENT_TABLE ORDER BY Value)
AND Element NOT IN
(SELECT TOP 50 PERCENT Element FROM ELEMENT_TABLE ORDER BY Value)

The problem is that I cannot seem to pass a variable to this percentage
so if I want another percentage range set, I ave to define another
function.

My question therefore is does anyone know either

(1) a way I can pass a variable (n) to these SELECT n PERCENT
statements

OR

(2) an alternative way of doing this?

Many thanks!

Andrew

[Stu]

You can do this using dynamic SQL, like so:

DECLARE @SQL nvarchar(4000)
DECLARE @BeginRange int
DECLARE @EndRange int

SET @BeginRange = 50
SET @EndRange = 75

SET @SQL = 'SELECT Element from ELEMENT_TABLE
WHERE Element IN
(SELECT TOP ' + CONVERT(varchar(3), @EndRange) + ' PERCENT Element
FROM ELEMENT_TABLE ORDER BY Value)
AND Element NOT IN
(SELECT TOP ' + CONVERT(varchar(3), @BeginRange) + ' PERCENT
Element FROM ELEMENT_TABLE ORDER BY Value) '

EXEC (@SQL)
HTH,
Stu

[andrew]

that is excellent. Now all working :-) thanks.

[Erland Sommarskog]

[posted and mailed]

andrew (ac****@gmail.com) writes:

My question therefore is does anyone know either

(1) a way I can pass a variable (n) to these SELECT n PERCENT
statements

Use SQL 2005. :-)

Dynamic SQL is probably the way to go, but there are a couple of
caveats. Before you start to use dynamic SQL all over town, you
might want to learn more about it. I have an article on my web site
about it: http://www.sommarskog.se/dynamic_sql.html.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp