How to Select The Mid Row of a Table 3

[spradeepraj]

How to select the mid row of a table?
eg if there are 10 records
5th and 6th row should be displayed
if there are 9 records
the 5th row should be displayed

mail me : spradeepraj@dsqsoft.com
Delphidhasan

 

[Turkbear]

Hi, There is really no such thing as a MID row in an Oracle table ( or in any true RDBMS)..
You may be able to calculate how many rows there are and use a subquery with rownum to get the row that matches 1/2 of that value( rounded or truncated of course), but that will tell you nothing..Without some ordering factor that is meaningful, just getting the record that 'happens' to be returned as the 'middle' row has no real meaning.

What is it you actually want to do?

 

[blom0344]

Apart from the effect whether it serves any purpose, this is perhaps more complicated than it looks. I spent half an hour on it ,but ran into all sorts of trouble. First you would have to check whether the table has an even or uneven amount of records. Then the ROWNUM (as a pseudo column !!) starts letting you down in WHERE clauses. Pretty tricky , this one. Cheers to the expert who shows the solution..... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[sem]

In fact it's qiute easy:

select * from (select rownum rn, a.* from <table> a)
where rn=(select avg(rownum) from <table>)

This will return the midst of returned result set as well as order number of this record, if any. You may ROUND or TRUNCate avg(rownum) to obtain result for even set.

Though, as Turkbear mentioned, there's no such thing in real world, because Oracle doesn't guarantee the order of records returned without ORDER BY clause, so this record may vary even in case the target table is untouched between executions.

 

[blom0344]

Sem,

Cheers.......... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Turkbear]

Hi,
Nice use of AVG sem - this may be helpful in an ordered set - I wouldn't have thought of that use...

Thanks, enjoy the star

 

[karluk]

I agree. Sem may think it's easy, but I would never have thought of this solution.