Wednesday, March 12, 2008

Select Middle Record

SELECT TOP 1 query in T-SQL helps to find the first or the last record of the table data sorted by some criteria. But what if we need to find exactly middle record entry in the table ? Below is a small T-SQL query snippet that demonstrates a technique how to get middle record in a single query

SELECT TOP 1 UserId
FROM (SELECT TOP 50 PERCENT UserId
FROM addressbook WITH (NOLOCK)
ORDER BY UserId ASC) AS T1
ORDER BY 1 DESC

2 comments:

CS said...

Some people might advise you to do a top, reverse sort and then top.. but because of the two sorts this may not be very efficient on large datasets. I use something like the following;


With
allusers as (select ROW_NUMBER() OVER (ORDER BY UniqUser) as RowNumber ,* from users)
select * from allusers where RowNumber between 2 AND 4


You only need a single sort, and if you keep it to the way you were sorting your query to begin with, it should be optimized out.

MikeGledhill said...

This script didn't quite work for me in SQL Server 2008. I had to use this:


select * FROM
(
select ROW_NUMBER() OVER (ORDER BY UserID) as RowNumber , * FROM tblUsers
) tmp
where tmp.RowNumber between 2 AND 3