45,000 Jobs - Get an Interview Call,  Post Your Resume Here
SURESHKUMAR.NET FORUMS
Registered Member Login:
Not a member? Register today!



Welcome to the SURESHKUMAR.NET FORUMS.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.




SQL to select middle record of a table

        

In this blog I will post all FAQs, code snippets, real time problems and solutions and any other links which I feel every one needs and can take advantage of.
Rate this Entry

SQL to select middle record of a table

Posted 13-11-07 at 11:48 PM by sk_kireeti
A decision has to taken carefully before we implement a solution for the two possibilities -
  1. When we have odd total number of rows
  2. When we have even total number of rows
My SQL will work differently in each case.

In first case (odd number of total rows), this SQL query will return middle record with out any conflict. Eg., If we have 9 records, this will display 5th record from table.

In second case (even number of total rows), this SQL query will return middle two records as we can't find single middle record. Eg., if we have 14 records in table, it will display both 7th and 8th rows.

select * from
(select rownum sno, empno,ename from emp)
where
sno in
(
select floor((count(*)+1)/2) eg from emp
union
select ceil((count(*)+1)/2) eg from emp
);


output -
--------------------------------------------------
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select * from
2 (select rownum sno, empno,ename from emp)
3 where
4 sno in
5 (
6 select floor((count(*)+1)/2) eg from emp
7 union
8 select ceil((count(*)+1)/2) eg from emp
9 );

SNO EMPNO ENAME
---------- ---------- ----------
7 7782 CLARK
8 7788 SCOTT

SQL> insert into emp(empno) values (20);

1 row created.

SQL> select count(*) from emp;

COUNT(*)
----------
15

SQL> select * from
2 (select rownum sno, empno,ename from emp)
3 where
4 sno in
5 (
6 select floor((count(*)+1)/2) eg from emp
7 union
8 select ceil((count(*)+1)/2) eg from emp
9 );

SNO EMPNO ENAME
---------- ---------- ----------
8 7782 CLARK

SQL>
Posted in Oracle SQL
Comments 1 Email Blog Entry
Total Comments 1

Comments

Old
then can you tell me how do we split the rows in the table into haft if it is odd......
permalink
Posted 03-03-08 at 11:06 PM by nithi_papu nithi_papu is offline
 

All times are GMT +6.5. The time now is 06:18 AM.

More Interview Questions Here...

Content Relevant URLs by vBSEO 3.3.0