Query to get Middle Record from a table [message #655196] |
Wed, 24 August 2016 10:22 |
|
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 #655220 is a reply to message #655196] |
Thu, 25 August 2016 02:25 |
John Watson
Messages: 8938 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 |
|
EdStevens
Messages: 1376 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.
|
|
|