Geek Answers Handbook

T-Sql Select * Between 30% and 40%

Question

  • How do I write a T-SQL stored procedure that allows me to select row percentages between X% and Y%?
  • So basically I would like to select lines between 30 PERCENT and 40 PERCENT .....

I know that you can do the following, but obviously this does not allow the label to specify a set of lines between 2 percent.

SELECT TOP 50 PERCENT * FROM tblAssets

Help with thanks.

+6
sql sql-server tsql stored-procedures sql-server-2005
source share
2 answers

Updated Answer

 declare @NumRecords int SELECT @NumRecords = COUNT(*) FROM tblAssets; With Vals As ( SELECT tblAssets.AssetId ... , ROW_NUMBER() OVER ( order by tblAssets.AssetId) as RN FROM tblAssets ) SELECT tblAssets.AssetId ... FROM vals Where RN between 0.3*@NumRecords and 0.4*@NumRecords

I updated my answer as there were 2 problems with my original answer below

  • Performance - he was beaten by the embedded TOP solution
  • Accuracy is an unexpected aspect of NTILE that I did not know about.

If the number of lines in a section is not divisible by integer_expression, this will result in a group of two sizes that differ from each other member. Larger groups smaller groups in the order shown by the OVER clause. For example, if the total number of lines is 53 and the number of groups is five, the first three groups will have 11 lines, and the remaining two groups will have 10 lines each.

I got the following values ​​comparable to the nested TOP solution.

 SET STATISTICS IO ON SET STATISTICS TIME ON; DECLARE @NumRecords int SELECT @NumRecords = COUNT(*) FROM [master].[dbo].[spt_values]; WITH Vals As ( SELECT [number] , ROW_NUMBER() OVER ( order by [number]) as RN FROM [master].[dbo].[spt_values] ) SELECT [number] FROM vals Where RN BETWEEN 0.30*@NumRecords AND 0.40*@NumRecords

gives

Table 'spt_values'. Scan count 1, logical read 8, physical read 0, read-ahead reads 0, logical reads lob 0, physical read lob 0, lob read-ahead reads 0.

Table 'spt_values'. The number of scans is 1, logical reads 5, physical reads 0, reads ahead reads 0, forehead logically reads 0, lob physical reads 0, reads with reads LOB 0.

 SELECT TOP 25 PERCENT [number] FROM ( SELECT TOP 40 PERCENT [number] FROM [master].[dbo].[spt_values] ORDER BY [number] ASC ) TOP40 ORDER BY [number] DESC

gives

Table "Desktop". Number of scans 1, logical read 4726, physical read 0, read-ahead reads 0, logical reads lob 0, physical reads lob 0, lob read-ahead reads 0.

Table 'spt_values'. The number of scans is 1, the logical reads 8, the physical reads 0, the forward reads 0, the forehead logically reads 0, lob the physical reads 0, the read with the read LOB 0.

Original answer

 With Vals As ( SELECT tblAssets.AssetId ... , NTILE (100) OVER ( order by tblAssets.AssetId) as Pct FROM tblAssets ) SELECT * FROM vals Where Pct between 30 and 40
+13
source share

I came up with this myself .......

 SELECT TOP 40 * INTO #TOP40 FROM CCDtblAssets SELECT * FROM #TOP40 WHERE ASSETID NOT IN (SELECT TOP 30 ASSETID FROM #TOP40)

Although I like Martins answer.

0
source share

More articles:


All Articles