Solved

Find middle row in table

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

695 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