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

```CodeSELECT * FROM table_name WHERE ROWNUM <=
(SELECT CASE MOD(COUNT(1),2)
WHEN 0 THEN(COUNT(1)/2) + 1
ELSE ROUND(COUNT(1)/2) END FROM table_name)
MINUS
SELECT * FROM table_name
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

```CodeTO SELECT the middle rows:
SELECT * FROM TABLE offset 50 rows fetch next 25 rows only
```

#### Nazeera Jaffar

• Sep 26th, 2012

The below code selects the exact middle row from the table

`CodeSELECT * FROM TABLE WHERE rownum=trunc(SELECT count(*)/2 FROM TABLE) `

#### Kishore Kishore Profile Answers by Kishore Kishore

• Mar 10th, 2015

```CodeSELECT *
FROM (  SELECT EMPNO, DENSE_RANK () OVER (ORDER BY EMPNO) RANK
FROM EMP
GROUP BY EMPNO)
WHERE RANK = (SELECT COUNT (*) / 2 FROM EMP);```

#### Naveen Hokrana

• Feb 2nd, 2016

`CodeSELECT * 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:

```CodeSELECT * 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:```

#### Visal

• Aug 13th, 2016

```CodeSELECT *
FROM EMP
WHERE ROWID = (SELECT MAX(ROWID)/2 FROM EMP)
GROUP BY ROWID;
```

#### Kaushik

• Aug 7th, 2017

```Code    SELECT e1.*
FROM   employees e1,
(SELECT e.employee_id,
ROW_NUMBER()
over (
ORDER BY employee_id ) rn,
FLOOR(COUNT(*)
over ( ) / 2)      mi,
CEIL(COUNT(*)
over ( ) / 2)       ma
FROM   employees e) e2
WHERE  e2.employee_id = e1.employee_id
AND rn BETWEEN mi AND ma;  ```

#### SuryaRS Profile Answers by SuryaRS

• Sep 11th, 2017

Below code works to have middle record of atable

```CodeSELECT a.* FROM employee a WHERE rownum <= (SELECT trunc(count(*)/2) FROM employee)
minus
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)

`CodeSELECT * 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

```CodeSELECT * FROM emp WHERE rownum<=9
MINUS
SELECT * FROM emp WHERE rownum<5```