在SQL Server中选择中间行

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

我有一个表,我想要选择最后10%的行,偏移10%(所以我想选择最后80-90%的数据)。

I wrote the following query

我写了以下查询

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

But I receive the following error:

但是我收到以下错误:

Line 5: Incorrect syntax near 'OFFSET'.

第5行:'OFFSET'附近的语法不正确。

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

我究竟做错了什么?我使用的Microsoft SQL Server 2012应与OFFSET兼容

3 个解决方案

#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

#2

For your error message, is your database set to backwards compatibility mode?

对于您的错误消息,您的数据库是否设置为向后兼容模式?

The offset expression only allows you to specify row numbers, not percentages. You can select the 80-90 percentile like:

偏移表达式仅允许您指定行号,而不是百分比。您可以选择80-90百分位数:

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

#3

You can use a simple 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

#1