# Display Middle Record

How to display middle record in a given table?

#### taaznyonker Profile Answers by 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)

#### taaznyonker Profile Answers by taaznyonker

• Mar 31st, 2010

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)

#### sampra Profile Answers by 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)

#### 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.

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

#### Kishore Kishore Profile Answers by Kishore Kishore

• Mar 10th, 2015

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

#### 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

#### Shivam

• Feb 13th, 2016

WRONG You cannot put = in rownum

#### sumanamara2016 Profile Answers by sumanamara2016

• Mar 18th, 2016

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:

#### Visal

• Aug 13th, 2016

Code
1. SELECT *

2. FROM EMP

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

4. GROUP BY ROWID;

5.

#### Kaushik

• Aug 7th, 2017

Code
1.     SELECT e1.*

2.     FROM   employees e1,

3.            (SELECT e.employee_id,

4.                      over (

5.                        ORDER BY employee_id ) rn,

6.                    FLOOR(COUNT(*)

7.                            over ( ) / 2)      mi,

8.                    CEIL(COUNT(*)

9.                           over ( ) / 2)       ma

10.             FROM   employees e) e2

11.     WHERE  e2.employee_id = e1.employee_id

12.            AND rn BETWEEN mi AND ma;

#### SuryaRS Profile Answers by 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)

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

#### mahesh

• Feb 8th, 2018

>= is not working in rownum
check it once

#### Manthan

• Aug 21st, 2018

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

#### shreyas varchasvi

• Oct 15th, 2018

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

#### 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