Experts Exchange connects you with the people and services you need so you can get back to work.

Find middle row in table

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.

drl1DirectorAsked: 2014-02-04

Who is Participating?

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2014-02-04

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

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2014-02-04

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

drl1DirectorAuthor Commented: 2014-02-04

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!).

LVL 40

lcohanDatabase AnalystCommented: 2014-02-04

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));