CODEWITHSUNDEEP
sql-serversql-server-2012
Bijan
Bijan

Reputation: 8602

Select Middle Rows in 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).

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'.

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

Upvotes: 6

Views: 30759

Answers (7)

ganesh kavhar
ganesh kavhar

Reputation: 33

with this code, rownum get a proper rownum from a list of records available in table and pick a middle one record from them and display as follows:

SELECT * FROM
    (SELECT E.*, ROWNUM RM FROM MYCODE E)
    WHERE RM=(SELECT COUNT(*)/2 FROM MYCODE);

Required Output of middle record from table:

enter image description here

Upvotes: 0

Shafi Miah
Shafi Miah

Reputation: 1

declare @middle1 as int
set @middle1 = ((select COUNT(*) from [table] )+1)/2
declare @middle2 as int
set @middle2 = ((select COUNT(*) from [table] ))/2

select * from 
(select ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as R, * from [table] where (select COUNT(*) from [table] ) % 2 = 0) T2
where  (T2.R - @middle2 = 0) or (T2.R- @middle1 = 0)
union
select * from 
(select ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as R, * from [table] where (select COUNT(*) from [table] ) % 2 != 0) T2
where  T2.R - @middle1 = 0

Upvotes: -3

user2883951
user2883951

Reputation:

If your looking for a way to present, to a web page for example, blocks of data..

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 then second block of 10 (11 - 20) and so one.

Now, a word of caution. If you data changes frequently, this may suffer as it will give you the first 10 rows (or rows 50 to 60) at the time the query is done.

So, if new data is being added, that throws off the list, be warned. If you looking at a list of computers, for example, and someone adds a new server named "AAA", and your looking at the middle of the list, what was item 50 in one query, may be item 49 in the second query. (I hope I didn't confuse that even more).

Upvotes: 0

user10508443
user10508443

Reputation: 1

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

Upvotes: -1

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

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

Upvotes: 5

Andomar
Andomar

Reputation: 238086

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:

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

Upvotes: 1

Related Questions