Home » SQL & PL/SQL » SQL & PL/SQL » select data using partition by clause where value of column is max (oracle 10g)
|
|
|
|
|
|
|
|
|
|
| Re: select data using partition by clause where value of column is max [message #622360 is a reply to message #622301] |
Mon, 25 August 2014 12:50   |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
This will be more efficient:
SQL> with t1 as (
2 SELECT empno,
3 ename,
4 deptno,
5 COUNT(empno) OVER(PARTITION BY deptno) AS ttlcount
6 FROM emp e
7 ),
8 t2 as (
9 SELECT empno,
10 ename,
11 deptno,
12 ttlcount,
13 MAX(ttlcount) OVER() AS maxttlcount
14 FROM t1
15 )
16 SELECT empno,
17 ename,
18 deptno
19 FROM t2
20 WHERE ttlcount =maxttlcount
21 /
EMPNO ENAME DEPTNO
---------- ---------- ----------
7521 WARD 30
7844 TURNER 30
7499 ALLEN 30
7900 JAMES 30
7698 BLAKE 30
7654 MARTIN 30
6 rows selected.
SQL>
It will do just one, not two table scans:
SQL> explain plan for
2 with data as (
3 SELECT e.empno, e.ename, e.deptno,
4 COUNT (empno) OVER (PARTITION BY deptno) AS ttlcount
5 FROM scott.emp e
6 )
7 SELECT e.empno, e.ename, e.deptno
8 from data e
9 where ttlcount=(select max(ttlcount) from data)
10 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2831958066
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 644 | 8 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 644 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 182 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 13 | | |
| 5 | VIEW | | 14 | 182 | 4 (25)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | WINDOW SORT | | 14 | 98 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TTLCOUNT"= (SELECT MAX("TTLCOUNT") FROM (SELECT
"E"."EMPNO" "EMPNO","E"."ENAME" "ENAME","E"."DEPTNO"
"DEPTNO",COUNT("EMPNO") OVER ( PARTITION BY "DEPTNO") "TTLCOUNT" FROM
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"SCOTT"."EMP" "E") "DATA"))
22 rows selected.
SQL> explain plan for
2 with t1 as (
3 SELECT empno,
4 ename,
5 deptno,
6 COUNT(empno) OVER(PARTITION BY deptno) AS ttlcount
7 FROM emp e
8 ),
9 t2 as (
10 SELECT empno,
11 ename,
12 deptno,
13 ttlcount,
14 MAX(ttlcount) OVER() AS maxttlcount
15 FROM t1
16 )
17 SELECT empno,
18 ename,
19 deptno
20 FROM t2
21 WHERE ttlcount = maxttlcount
22 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 255894745
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01 |
| 2 | WINDOW BUFFER | | 14 | 644 | 4 (25)| 00:00:01 |
| 3 | VIEW | | 14 | 644 | 4 (25)| 00:00:01 |
| 4 | WINDOW SORT | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TTLCOUNT"="MAXTTLCOUNT")
17 rows selected.
SQL>
SY.
|
|
|
|
| Re: select data using partition by clause where value of column is max [message #622388 is a reply to message #622337] |
Tue, 26 August 2014 01:02 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 25 August 2014 15:35
Quote: Else, if you want either 20 or 30, then you need to restrict the rows, ROWNUM = 1 will do that.
Not the best way as not deterministic.
Better add a condition to the query requirements to make them deterministic (like "the smallest department number with the greatest number of employees").
Good point.
I am thinking of RANK for 20 and 30, ROW_NUMBER for 20 or 30.
-- A tie of count for deptno 20 and 30, 5 in each
DELETE FROM EMP WHERE EMPNO=7654;
-- Records of deptno 20 and 30
WITH t1
AS (SELECT DEPTNO,
rank ()
over (
ORDER BY COUNT (*) DESC) AS RNK
FROM emp
GROUP BY deptno)
SELECT *
FROM EMP
WHERE deptno in (SELECT deptno
FROM t1
WHERE RNK = 1);
-- Records of deptno 20 or 30
WITH t1
AS (SELECT DEPTNO,
row_number ()
over (
ORDER BY COUNT (*) DESC) AS RN
FROM emp
GROUP BY deptno)
SELECT *
FROM emp
WHERE deptno = (SELECT deptno
FROM t1
WHERE RN = 1);
rollback;
But, as SY said, this will do two table scans, so not efficient.
|
|
|
|
| Re: select data using partition by clause where value of column is max [message #622457 is a reply to message #622388] |
Tue, 26 August 2014 11:16 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SQL> update emp set deptno=20 where ename = 'KING'; -- now both deptno 20 and 30 have 6 employees
1 row updated.
SQL> with t1 as (
2 SELECT empno,
3 ename,
4 deptno,
5 COUNT(empno) OVER(PARTITION BY deptno) AS ttlcount
6 FROM emp e
7 ),
8 t2 as (
9 SELECT empno,
10 ename,
11 deptno,
12 ttlcount,
13 MAX(ttlcount) OVER() AS maxttlcount
14 FROM t1
15 )
16 SELECT empno,
17 ename,
18 deptno
19 FROM t2
20 WHERE ttlcount = maxttlcount
21 /
EMPNO ENAME DEPTNO
---------- ---------- ----------
7788 SCOTT 20
7876 ADAMS 20
7839 KING 20
7369 SMITH 20
7902 FORD 20
7566 JONES 20
7499 ALLEN 30
7844 TURNER 30
7698 BLAKE 30
7654 MARTIN 30
7900 JAMES 30
EMPNO ENAME DEPTNO
---------- ---------- ----------
7521 WARD 30
12 rows selected.
SQL> with t1 as (
2 SELECT empno,
3 ename,
4 deptno,
5 COUNT(empno) OVER(PARTITION BY deptno) AS ttlcount
6 FROM emp e
7 ),
8 t2 as (
9 SELECT empno,
10 ename,
11 deptno,
12 RANK() OVER(ORDER BY ttlcount desc,deptno) AS flag
13 FROM t1
14 )
15 SELECT empno,
16 ename,
17 deptno
18 FROM t2
19 WHERE flag = 1
20 /
EMPNO ENAME DEPTNO
---------- ---------- ----------
7788 SCOTT 20
7566 JONES 20
7902 FORD 20
7369 SMITH 20
7839 KING 20
7876 ADAMS 20
6 rows selected.
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 18:18:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
