Solved

Find middle row in table

Posted on 2014-02-04
4
244 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
  • 2
4 Comments
 
LVL 142

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 142

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 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 28
migrate a SQL 2008 to 2016, 2 26
SQL Insert parts by customer 12 31
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
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 …
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…
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.

816 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now