Home » SQL & PL/SQL » SQL & PL/SQL » Query to get Middle Record from a table (Oracle 11g)
Query to get Middle Record from a table [message #655196] Wed, 24 August 2016 10:22 Go to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Please give me a better query to return middle record from a table. Count could be odd or even.
Query should work
a) if table has 14 records then it should give 7th record as an output
b) if table has 15 records then it should give 8th record as an output


My query is below:
SELECT * FROM 
(SELECT EMP.*, ROW_NUMBER() OVER (ORDER BY EMPNO)AS RNO FROM EMP 
WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM EMP))
WHERE RNO=(SELECT COUNT(*)/2 FROM EMP);

My Output:
EMPNO   ENAME   JOB     MGR     HIREDATE                SAL     COMM   DEPTNO  RNO
7782	CLARK	MANAGER	7839	09-JUN-1981 00:00:00	2450		10	7
Re: Query to get Middle Record from a table [message #655198 is a reply to message #655196] Wed, 24 August 2016 10:33 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
As long as your using empno for the order and it is a primary key then you almost have it

SELECT * FROM 
(SELECT EMP.*, ROW_NUMBER() OVER (ORDER BY EMPNO)AS RNO FROM EMP) 
WHERE RNO=(SELECT ceil(COUNT(*)/2) FROM EMP);

[Updated on: Wed, 24 August 2016 11:17]

Report message to a moderator

Re: Query to get Middle Record from a table [message #655199 is a reply to message #655198] Wed, 24 August 2016 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bill, a closing parenthesis is missing at the end of line 2.

Re: Query to get Middle Record from a table [message #655200 is a reply to message #655199] Wed, 24 August 2016 10:55 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
OOPS. Thank you Michel. I missed it when copy/pasting. I will edit the entry to put it in

[Updated on: Wed, 24 August 2016 11:16]

Report message to a moderator

Re: Query to get Middle Record from a table [message #655201 is a reply to message #655198] Wed, 24 August 2016 11:55 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Thanks
Re: Query to get Middle Record from a table [message #655202 is a reply to message #655201] Wed, 24 August 2016 11:57 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You welcome
Re: Query to get Middle Record from a table [message #655204 is a reply to message #655202] Wed, 24 August 2016 12:22 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
A faster way to get the middle record follows. This doesn't require another scan of the table

SELECT *
  FROM (SELECT Emp.*,
               ROW_NUMBER () OVER (ORDER BY Empno) AS Rno, 
               count(*) over () tot_recs 
          FROM Emp)
where rno = ceil(tot_recs/2);

[Updated on: Wed, 24 August 2016 12:22]

Report message to a moderator

Re: Query to get Middle Record from a table [message #655220 is a reply to message #655196] Thu, 25 August 2016 02:25 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
Here's a syntactical variation. It isn't as efficient as BillB's solution, but I do like to use the row limit clause where possible: it is a nice way to generate analytic SQLs and (I think) much easier to understand.
orclz>
orclz> select * from emp order by empno offset (select count(*)/2 -1 from emp) rows fetch next 1 row only;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 532028908

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |    14 |  1582 |     8  (13)| 00:00:01 |
|*  1 |  VIEW                  |        |    14 |  1582 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT          |        |    14 |  1218 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | EMP    |    14 |  1218 |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE       |        |     1 |       |            |          |
|   5 |    INDEX FAST FULL SCAN| PK_EMP |    14 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
              ( (SELECT COUNT(*)/2-1 FROM "EMP" "EMP")>=0) THEN
              FLOOR(TO_NUMBER(TO_CHAR( (SELECT COUNT(*)/2-1 FROM "EMP" "EMP")))) ELSE
              0 END +1 AND "from$_subquery$_002"."rowlimit_$$_rownumber"> (SELECT
              COUNT(*)/2-1 FROM "EMP" "EMP"))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz>


--update: a slightly more elegant statement:
select * from emp order by empno offset (select count(*)/2 -1 from emp) rows fetch next row only;

[Updated on: Thu, 25 August 2016 02:58]

Report message to a moderator

Re: Query to get Middle Record from a table [message #655233 is a reply to message #655196] Thu, 25 August 2016 06:37 Go to previous message
EdStevens
Messages: 1321
Registered: September 2013
Senior Member
Rows in a table are like balls in a basket. There is no concept of "first", "last", or "middle".

When you SELECT, you can apply an ORDER BY. Then your result set (not the table) will have a first, last, and middle.

You need to clarify your intent and the business requirement. Even if you do define a column on which to apply an ORDER BY, I have a hard time imagining such a query returning anything that would actually be useful to a business.
Previous Topic: Union of 4 different columns in Single Row
Next Topic: how to restrict set of data in oracle
Goto Forum:
  


Current Time: Sun Jun 13 16:18:25 CDT 2021