SQL Server 2000: Field Length in 1/3 (or 2/3) Records

Question

Is there an easier / cleaner way to do this with SQL Server 2000?

Every few days I need to do this.

First, I'll see how many records we have:

SELECT COUNT(*) FROM MyTable

Then I list all the lengths of a certain field:

SELECT LEN(MyText)
FROM MyTable
ORDER BY LEN(MyText) ASC

Then I need to scroll down 1/3 of the way ... and mark the value. Then I need to scroll down 2 / 3rds of the path ... and mark the value. And finally, the last meaning.

I need to know x, y and z:

 33% of the records have this field with a length under x bytes
 66% of the records have this field with a length under y bytes
100% of the records have this field with a length under z bytes

Answer 1

In SQL 2005, you could probably use ranking functions for this. In SQL 2000, I think you are stuck doing something like this.

DECLARE @RC INT 

CREATE TABLE #lengths
(
id INT IDENTITY(1,1),
[length] INT
)

INSERT INTO #lengths
SELECT LEN(MyText)
FROM MyTable
ORDER BY LEN(MyText) ASC


SET @rc= @@ROWCOUNT

SELECT [length] 
FROM #lengths 
WHERE id IN 
(@rc/3, (2*@rc)/3, @rc)

Answer 2

, - :

SELECT
 x1.l AS Length,
 x1.n      * 1e2 / (SELECT COUNT(*) FROM MyTable) AS [Percent],
 SUM(x2.n) * 1e2 / (SELECT COUNT(*) FROM MyTable) AS CumPercent
FROM (
 SELECT LEN(MyText) AS l, COUNT(*) AS n
 FROM MyTable
 GROUP BY LEN(MyText)
) AS x1
LEFT JOIN (
 SELECT LEN(MyText) AS l, COUNT(*) AS n
 FROM MyTable
 GROUP BY LEN(MyText)
) AS x2
 ON x2.l <= x1.l
GROUP BY x1.l, x1.n
ORDER BY x1.l