Display Middle Record

How to display middle record in a given table?

Questions by shameem_chandu

Showing Answers 1 - 54 of 54 Answers

taaznyonker

  • Mar 31st, 2010
 

BELOW example will give the middle row of the table(not sorted).

eg:

SELECT * FROM EMP WHERE ROWNUM <=(SELECT COUNT(1)/2 FROM EMP)
MINUS
SELECT * FROM EMP WHERE ROWNUM <(SELECT COUNT(1)/2 FROM EMP)

  • Aug 11th, 2011
 

The following query works as follows: If the table has ten records, it will display the 5th and 6th record and if it has some 11 records, will display 6th record alone.

Code
  1. SELECT * FROM table_name WHERE ROWNUM <=

  2. (SELECT CASE MOD(COUNT(1),2)

  3. WHEN 0 THEN(COUNT(1)/2) + 1

  4. ELSE ROUND(COUNT(1)/2) END FROM table_name)

  5. MINUS

  6. SELECT * FROM table_name

  7. WHERE ROWNUM < (SELECT (COUNT(1)/2) FROM table_name)

  Was this answer useful?  Yes

sampra

  • Mar 6th, 2012
 

SELECT * FROM EMP WHERE ROWNUM <=(SELECT COUNT(1)/2 FROM EMP)
MINUS
SELECT * FROM EMP WHERE ROWNUM >=(SELECT COUNT(1)/2 FROM EMP)

  Was this answer useful?  Yes

Nazeera Jaffar

  • Sep 26th, 2012
 

The below code returns the rows from 50-74

Code
  1. TO SELECT the middle rows:

  2.         SELECT * FROM TABLE offset 50 rows fetch next 25 rows only

  3.              

  Was this answer useful?  Yes

Nazeera Jaffar

  • Sep 26th, 2012
 

The below code selects the exact middle row from the table

Code
  1. SELECT * FROM TABLE WHERE rownum=trunc(SELECT count(*)/2 FROM TABLE)

  Was this answer useful?  Yes

Code
  1. SELECT *

  2.   FROM (  SELECT EMPNO, DENSE_RANK () OVER (ORDER BY EMPNO) RANK

  3.             FROM EMP

  4.         GROUP BY EMPNO)

  5.  WHERE RANK = (SELECT COUNT (*) / 2 FROM EMP);


  Was this answer useful?  Yes

Naveen Hokrana

  • Feb 2nd, 2016
 

Code
  1. SELECT * FROM (SELECT FIRST_NAME,LAST_NAME,EMPLOYEE_ID,DENSE_RANK() OVER(ORDER BY EMPLOYEE_ID) DRK FROM EMPLOYEES) WHERE DRK=SELECT TRUNC(COUNT(*)/2) FROM EMPLOYEES

  Was this answer useful?  Yes

Shivam

  • Feb 13th, 2016
 

WRONG You cannot put = in rownum

  Was this answer useful?  Yes

It will not work
SELECT * FROM EMP WHERE rownum=trunc(SELECT count(*)/2 FROM EMP)
Error at Command Line : 42 Column : 38
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:

Code
  1. SELECT * FROM EMP WHERE ROWNUM=TRUNC(SELECT COUNT(*)/2 FROM EMP)

  2. Error AT Command Line : 42 COLUMN : 38

  3. Error report -

  4. SQL Error: ORA-00936: missing expression

  5. 00936. 00000 -  "missing expression"

  6. *Cause:    

  7. *Action:

  Was this answer useful?  Yes

Visal

  • Aug 13th, 2016
 

Code
  1. SELECT *

  2. FROM EMP

  3. WHERE ROWID = (SELECT MAX(ROWID)/2 FROM EMP)

  4. GROUP BY ROWID;

  5.  

  Was this answer useful?  Yes

Kaushik

  • Aug 7th, 2017
 

Code
  1.     SELECT e1.*

  2.     FROM   employees e1,

  3.            (SELECT e.employee_id,

  4.                    ROW_NUMBER()

  5.                      over (

  6.                        ORDER BY employee_id ) rn,

  7.                    FLOOR(COUNT(*)

  8.                            over ( ) / 2)      mi,

  9.                    CEIL(COUNT(*)

  10.                           over ( ) / 2)       ma

  11.             FROM   employees e) e2

  12.     WHERE  e2.employee_id = e1.employee_id

  13.            AND rn BETWEEN mi AND ma;  

  Was this answer useful?  Yes

SuryaRS

  • Sep 11th, 2017
 

Below code works to have middle record of atable

Code
  1. SELECT a.* FROM employee a WHERE rownum <= (SELECT trunc(count(*)/2) FROM employee)

  2. minus

  3. SELECT a.* FROM employee a WHERE rownum <> (SELECT trunc(count(*)/2) FROM employee)

  Was this answer useful?  Yes

SATHIS KUMAR

  • Jan 23rd, 2018
 

SELECT * FROM (SELECT ROWNUM ID,E.* FROM EMP E) WHERE ID IN (SELECT COUNT(*) FROM EMP)

Code
  1. SELECT * FROM (SELECT ROWNUM ID,E.* FROM EMP E) WHERE ID IN (SELECT COUNT(*) FROM EMP)

  Was this answer useful?  Yes

mahesh

  • Feb 8th, 2018
 

>= is not working in rownum
check it once

  Was this answer useful?  Yes

Manthan

  • Aug 21st, 2018
 

Its displaying:
ERROR at line 1:
ORA-00936: missing expression

  Was this answer useful?  Yes

shreyas varchasvi

  • Oct 15th, 2018
 

in rownum we cant use >= operator, only < ,< = operator will work.

  Was this answer useful?  Yes

Bibhudatta Panda

  • Jun 18th, 2019
 

Suppose I want middle record 5-9 records

Code
  1. SELECT * FROM emp WHERE rownum<=9

  2. MINUS

  3. SELECT * FROM emp WHERE rownum<5

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions