Home » SQL & PL/SQL » SQL & PL/SQL » subquery with group by issue (oracle 11)
subquery with group by issue [message #649687] Mon, 04 April 2016 02:15 Go to next message
rlicata
Messages: 4
Registered: April 2016
Junior Member
Hello All,
as a newbie, I would like to execute a query based on 1 table : T_XMON_ALARMS

CREATE TABLE "XXXX"."T_XMON_ALARMS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"ALARM_KEY" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
        ...
	"LAST_UPDATED_AT" TIMESTAMP (6), 
	"STATUS" NUMBER DEFAULT 0 NOT NULL ENABLE, 
	 CONSTRAINT "T_XMON_ALARMS_PK" PRIMARY KEY ("ID")


DATA

ID   ALARM_KEY SEVERITY LAST_UPDATED_AT
1	A001	1	30-MAR-16 09.08.51.421000000 AM
2	A001	2	07-MAR-16 09.08.59.707000000 AM
3	A002	3	16-MAR-16 09.09.04.730000000 AM
4	A002	1	12-MAR-16 09.09.12.446000000 AM
6	A002	2	31-MAR-16 09.06.43.229000000 AM
7	A004	2	31-MAR-16 09.21.34.638000000 AM
8	AA003	1	16-MAR-16 09.09.12.446000000 AM
9	AA003	1	14-MAR-16 09.09.12.446000000 AM
10	A000	1	16-MAR-16 09.09.12.446000000 AM
11	AA003	1	16-MAR-16 09.09.12.446000000 AM
5	A001	2	30-MAR-16 09.09.19.700000000 AM



the query must returns the ALARM_KEY and a counter but ordered by the LAST_UPDATED_AT DESC. (get the newest alarm as first row)
So I tried the following query but the order is not correct.

SELECT ALARM_KEY, COUNT(*) AS CPT FROM (SELECT * FROM T_XMON_ALARMS WHERE STATUS = 0 ORDER BY LAST_UPDATED_AT DESC)
GROUP BY ALARM_KEY;


I would like to get :

A004 1
A002 3
A001 3

Someone can help?

Thanks & regards

Raph

[mod-edit: code tags added by bb]

[Updated on: Mon, 04 April 2016 14:41] by Moderator

Report message to a moderator

Re: subquery with group by issue [message #649688 is a reply to message #649687] Mon, 04 April 2016 02:18 Go to previous messageGo to next message
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Your ORDER BY clause is in the subquery, where it is useless because the rows must be re-ordered in order to do the aggregation. You need to move it to the outer query.

--update:
I just had a another link. Your logic is impossible. You cannot ORDER BY a column that you are not projecting. You need to re-think what you are trying to achieve.

[Updated on: Mon, 04 April 2016 02:21]

Report message to a moderator

Re: subquery with group by issue [message #649689 is a reply to message #649688] Mon, 04 April 2016 02:24 Go to previous messageGo to next message
rlicata
Messages: 4
Registered: April 2016
Junior Member
Hi John,

If I move the ORDER BY to the outer query, I need to add LAST_UPDATED_AT in the SELECT and in the GROUP BY, this way I cannot have a counter only on ALARM_KEY, or I miss something Embarassed

Thanks

Report message to a moderator

Re: subquery with group by issue [message #649690 is a reply to message #649688] Mon, 04 April 2016 02:41 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 04 April 2016 08:18
You cannot ORDER BY a column that you are not projecting.

Generally speaking you can order by a column you're not selecting, but not if you're using group by like the OP is.

Report message to a moderator

Re: subquery with group by issue [message #649691 is a reply to message #649690] Mon, 04 April 2016 02:42 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually that should be you can't order by a column you're not selecting if you're using aggregate functions.

Report message to a moderator

Re: subquery with group by issue [message #649692 is a reply to message #649688] Mon, 04 April 2016 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You cannot ORDER BY a column that you are not projecting.


Yes you can:
SQL> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL> select ename from emp order by deptno, empno;
ENAME
----------
CLARK
KING
MILLER
SMITH
JONES
SCOTT
ADAMS
FORD
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

14 rows selected.

SQL> select deptno, empno, ename from emp order by deptno, empno;
    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK
        10       7839 KING
        10       7934 MILLER
        20       7369 SMITH
        20       7566 JONES
        20       7788 SCOTT
        20       7876 ADAMS
        20       7902 FORD
        30       7499 ALLEN
        30       7521 WARD
        30       7654 MARTIN
        30       7698 BLAKE
        30       7844 TURNER
        30       7900 JAMES

14 rows selected.

You cannot do it when you have a set operator (UNION, INTERSECT, MINUS).

Report message to a moderator

Re: subquery with group by issue [message #649693 is a reply to message #649690] Mon, 04 April 2016 02:49 Go to previous messageGo to next message
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
Senior Member
cookiemonster wrote on Mon, 04 April 2016 08:41
John Watson wrote on Mon, 04 April 2016 08:18
You cannot ORDER BY a column that you are not projecting.

Generally speaking you can order by a column you're not selecting, but not if you're using group by like the OP is.

So you can! It had never occurred to me that one can do that. Looking at the execution plan details, the sort column does in fact get projected along with the named columns. That makes sense when one considers the basic SQL execution cycle:
1. select the rows
2. do the aggregation
3. project the columns
4. perform the sort

Report message to a moderator

Re: subquery with group by issue [message #649694 is a reply to message #649691] Mon, 04 April 2016 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Mon, 04 April 2016 09:42
Actually that should be you can't order by a column you're not selecting if you're using aggregate functions.


Yes you can:
SQL> select deptno, sum(sal) from emp group by deptno, job order by job;
    DEPTNO   SUM(SAL)
---------- ----------
        20       6000
        10       1300
        20       1900
        30        950
        10       2450
        20       2975
        30       2850
        10       5000
        30       5600

9 rows selected.

SQL> select deptno, sum(sal), job from emp group by deptno, job order by job;
    DEPTNO   SUM(SAL) JOB
---------- ---------- ---------
        20       6000 ANALYST
        10       1300 CLERK
        20       1900 CLERK
        30        950 CLERK
        10       2450 MANAGER
        20       2975 MANAGER
        30       2850 MANAGER
        10       5000 PRESIDENT
        30       5600 SALESMAN

9 rows selected.

Currently I don't see the usage of this but I remember I once encountered a case it was used.

Report message to a moderator

Re: subquery with group by issue [message #649696 is a reply to message #649694] Mon, 04 April 2016 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Good to know.
@rlicata - since there are multiple values of last_updated_at per alarm key you're going to need an inner query that gets the max(last_updated_at) for each alarm_key along with the count then an outer query that selects alarm_key and count and orders by the max(last_updated_at).

[Updated on: Mon, 04 April 2016 03:56]

Report message to a moderator

Re: subquery with group by issue [message #649697 is a reply to message #649696] Mon, 04 April 2016 04:08 Go to previous messageGo to next message
rlicata
Messages: 4
Registered: April 2016
Junior Member
Thanks you,

seems to work with the following query :

SELECT *
from (SELECT ALARM_KEY, COUNT(*) AS countx FROM T_XMON_ALARMS WHERE STATUS = 0 GROUP BY ALARM_KEY) tab1
JOIN (SELECT ALARM_KEY, MAX(LAST_UPDATED_AT) AS lastUpdated FROM T_XMON_ALARMS WHERE STATUS = 0 GROUP BY ALARM_KEY ORDER BY lastUpdated DESC) tab2
on tab1.ALARM_KEY =tab2.ALARM_KEY;

Report message to a moderator

Re: subquery with group by issue [message #649698 is a reply to message #649697] Mon, 04 April 2016 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again: Please read and APPLY How to use [code] tags and make your code easier to read.

And correctly format your statement.
If you don't know how to do it, learn it using SQL Formatter.

[Updated on: Mon, 04 April 2016 04:25]

Report message to a moderator

Re: subquery with group by issue [message #649699 is a reply to message #649697] Mon, 04 April 2016 04:19 Go to previous messageGo to next message
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
Senior Member
That is not a correct solution. I think it relies on Cost Based Optimizer choosing to use a nested loop join with your tab2 as the outer row set. If it happens to choose, for example, a sort merge join the final ordering will be on alarm_key, not lastupdated. Hint it, and you will see.

Report message to a moderator

Re: subquery with group by issue [message #649700 is a reply to message #649699] Mon, 04 April 2016 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
I was thinking more like this:
SELECT alerm_key, countx
FROM (SELECT alarm_key, max(last_updated_at) AS lastUpdated, count(*) AS countx 
      FROM t_xmon_alarms 
      WHERE status = 0 
      GROUP BY alarm_key
     )
ORDER BY lastUpdated DESC;

Report message to a moderator

Re: subquery with group by issue [message #649702 is a reply to message #649700] Mon, 04 April 2016 06:01 Go to previous messageGo to next message
rlicata
Messages: 4
Registered: April 2016
Junior Member
more simple indeed Smile

Thanks

Report message to a moderator

Re: subquery with group by issue [message #649703 is a reply to message #649702] Mon, 04 April 2016 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And above all easier to read and understand as it is formatted.

Report message to a moderator

Re: subquery with group by issue [message #649711 is a reply to message #649700] Mon, 04 April 2016 09:09 Go to previous message
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
Senior Member
No need for in-line view. ORDER BY accepts aggredations:

SELECT  alarm_key,
        count(*) AS countx 
  FROM  t_xmon_alarms 
  WHERE status = 0 
  GROUP BY alarm_key
  ORDER BY max(last_updated_at) DESC;


For example:

SQL> select  deptno,
  2          count(*) cnt
  3    from  emp
  4    group by deptno
  5    order by max(hiredate) desc
  6  /

    DEPTNO        CNT
---------- ----------
        20          5
        10          3
        30          6

SQL>


SY.

Report message to a moderator

Previous Topic: need help to find CityJail_8.sql script
Next Topic: Random distribution of sum
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 30 15:35:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.