Solved

Find middle row in table

Posted on 2014-02-04
4
245 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
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 viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

735 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