Select Middle Rows in SQL Server

Question

I have a table where I want to select the last 10% of the rows, offset by 10% (so I want to select the last 80-90% of the data).

I wrote the following query

SELECT TOP 10 PERCENT [col1], [col2] FROM [table] ORDER BY [col1] DESC OFFSET 10 ROWS 

But I get the following error:

Line 5: Invalid syntax next to "OFFSET".

What am I doing wrong? I am using Microsoft SQL Server 2012, which must be compatible with OFFSET

Answer 1

Try something like this ....

 SELECT TOP (50) PERCENT * FROM ( SELECT TOP (20) PERCENT [col1] ,[col2] FROM [table] ORDER BY [col1] DESC )T ORDER BY [col1] ASC 

Answer 2

Is your database set to backward compatibility mode for your error message?

The offset expression allows you to specify line numbers, not percentages. You can choose 80-90 percentiles, for example:

 select * from ( select 100.0 * row_number() over (order by FirstName desc) / count(*) over () as perc_pos from YourTable ) as SubQueryAlias where 80 <= perc_pos and perc_pos < 90 

Answer 3

You can use plain good old not in:

 SELECT TOP 10 PERCENT [col1], [col2] FROM [table] WHERE [col1] NOT IN ( SELECT TOP 10 PERCENT [col1] FROM [table] ORDER BY [col1] DESC ) ORDER BY [col1] DESC 

Answer 4

If you are looking for a way to present on a web page, for example, data blocks ..

Try

  WITH Ordered AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ServerName) AS 'RowNumber' FROM systems ) SELECT * FROM Ordered WHERE RowNumber BETWEEN 11 AND 20 

With this code, I was able to offer the user the first 10, then the second block of 10 (11 - 20) and so on.

Now a word of caution. If you change the data often, this may suffer, as this will give you the first 10 lines (or lines 50 to 60) during the execution of the request.

So if new data is added that is discarded from the list, be careful. If, for example, you are browsing the list of computers, and someone is adding a new server named "AAA", and you are browsing the middle of the list, what was item 50 in one request may be item 49 in the second request. (I hope I have not confused this even more).

Answer 5

 select top 1 * from Employee where empid in ( select top 50 percent empid from employee order by empid ) order by empid desc