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

 

Below query will help you

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
SELECT * 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

Code
TO 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

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

Kishore Kishore

 Profile Answers by Kishore Kishore 

• Mar 10th, 2015

 

Code
SELECT *


  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

 

Code
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
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:

Visal

• Aug 13th, 2016

 

Code
SELECT *


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

Code
SELECT 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)

Code
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
SELECT * FROM emp WHERE rownum<=9


MINUS


SELECT * FROM emp WHERE rownum<5