Solved

Find middle row in table

Posted on 2014-02-04
4
246 Views
Last Modified: 2014-02-04
It's as simply as the heading suggests. How do you return the middle row from a table? Select top 1 with order by foo asc or desc is fine for top and bottom, but how can you most easily select the contents of the middle row?

MS SQL 2008 in case it matters.
0
Comment
Question by:drl1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39833579
you need to use a ROW_NUMBER() function here, compared with the COUNT(*) result (or the max of the row numbering ...
;with data as ( select t.*, row_number() over (order by foo) rn from yourtable )
select * 
from data
where rn = ( select ( max(x.rn) / 2 ) + 1 from data x) 

Open in new window

now, what if the count(*) returns 2 (an even number...), what row to return? row 1 or row 2 ?

depending on what you want, you will use floor() or ceiling():
;with data as ( select t.*, row_number() over (order by foo) rn from yourtable )
select * 
from data
where rn = ( select floor( max(x.rn) / 2 ) + 1 from data x) 

Open in new window


hope this helps
0
 

Author Comment

by:drl1
ID: 39833604
Thanks for that. I've changed the field and table names to suit my requirement but t.* is not being recognised as valid code. It may just be me being slow (it has been a long day!).
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39833623
sorry, my fault, I have omitted the table alias "t" in the query
;with data as ( select t.*, row_number() over (order by foo) rn from yourtable t )
select * 
from data
where rn = ( select floor( max(x.rn) / 2 ) + 1 from data x) 

Open in new window

0
 
LVL 40

Expert Comment

by:lcohan
ID: 39833627
assuming your table has a serial key (IDENTITY) column like I have in this case that can be done simply like:

select * from Clients with (nolock)
where Clientid = (select count(Clientid)/2 from clients with (nolock));
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question