Forum Stats

  • 3,734,025 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

Analitic functions (problem with SELECT)

JackK
JackK Member Posts: 633 Bronze Badge
edited November 2010 in SQL & PL/SQL
Hi!

I've got a problem with analitic functions (I'm newbie in this topic).
I have a table gpw_notowania which have colums: not_open, not_minimum, not_maximum, not_close, not_volume, not_sp_id and not_date.
I need to receive from database the information: what is the open, minimum, maximum, close and sum of volume in every week? I have tried the code below but it tells me: ORA-01791 (marking not_date in ORDER clause).

Help me, please.

SELECT	distinct
	FIRST_VALUE(not_open) 	OVER (partition by to_char(not_date,'WW') ORDER BY not_date)		as open,
	MIN(not_minimum)	OVER (partition by to_char(not_date,'WW') ORDER BY not_date)		as minimum,
	MAX(not_maximum)	OVER (partition by to_char(not_date,'WW') ORDER BY not_date)		as maximum,
	FIRST_VALUE(not_close)	OVER (partition by to_char(not_date,'WW') ORDER BY not_date DESC)	as close,
	sum(not_volume)		OVER (partition by to_char(not_date,'WW'))				as volume
FROM	gpw_notowania
WHERE	not_sp_id = 80
ORDER BY not_date;

Comments

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    ORDER BY not_date;
    Use open instead.

    Nicolas.
  • JackK
    JackK Member Posts: 633 Bronze Badge
    Oracle error is missed but the resultset is not sorted as I want it to be - by not_date.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited November 2007
    sorry, misreading the query.

    Furthermore, you can experience problem if you have more than one year in your data with the following code :
    to_char(not_date,'WW')
    Nicolas.

    Message was edited by:
    N. Gasparotto
  • 584412
    584412 Member Posts: 1,329
    Remove the distinct keyword
  • JackK
    JackK Member Posts: 633 Bronze Badge
    I have found the solution ;-)
    select distinct open, minimum, maximum, close, volume, week from (
        select * from (
    	SELECT
    		FIRST_VALUE(not_open) OVER(partition by to_char(not_date,'WW') ORDER BY not_date) as open,
    		MIN(not_minimum) OVER(partition by to_char(not_date,'WW') ORDER BY to_char(not_date,'WW')) as minimum,
    		MAX(not_maximum) over(partition by to_char(not_date,'WW') ORDER BY to_char(not_date,'WW')) as maximum,
    		FIRST_VALUE(not_close) OVER(partition by to_char(not_date,'WW') ORDER BY not_date DESC) as close,
    		sum(not_volume) OVER (partition by to_char(not_data,'WW')) as volume,
    		to_char(not_date,'WW') as week,
    		not_date
    	  FROM gpw_notowania
    	 WHERE not_sp_id = 80
        ) order by not_date
    ) order by week;
    I don't know if this is optimal and best performance but it works :-]

    Thanks.
  • 584412
    584412 Member Posts: 1,329
    edited November 2007
    Try:
    SELECT	distinct to_char(not_date,'WW') week
    	FIRST_VALUE(not_open) 	OVER (partition by to_char(not_date,'WW') ORDER BY not_date)		as open,
    	MIN(not_minimum)	OVER (partition by to_char(not_date,'WW') ORDER BY not_date)		as minimum,
    	MAX(not_maximum)	OVER (partition by to_char(not_date,'WW') ORDER BY not_date)		as maximum,
    	FIRST_VALUE(not_close)	OVER (partition by to_char(not_date,'WW') ORDER BY not_date DESC)	as close,
    	sum(not_volume)		OVER (partition by to_char(not_date,'WW'))				as volume
    FROM	gpw_notowania
    WHERE	not_sp_id = 80
    ORDER BY week
    Message was edited by:
    JS1
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    This is an interesting question.
    create table SortWithDistinct(Val1,Val2,sortKey,SubSortKey) as
    select 1,3,10,1 from dual union all
    select 1,3,10,1 from dual union all
    select 1,3,10,1 from dual union all
    select 2,4,30,2 from dual union all
    select 2,4,30,2 from dual union all
    select 3,5,20,1 from dual union all
    select 3,5,20,1 from dual union all
    select 4,6,10,3 from dual union all
    select 5,5,10,2 from dual union all
    select 5,5,10,2 from dual union all
    select 9,9,10,4 from dual union all
    select 6,4,20,2 from dual union all
    select 6,4,20,2 from dual union all
    select 7,3,30,1 from dual union all
    select 7,3,30,1 from dual;
    select distinct Val1,Val2
    from SortWithDistinct
    order by sortKey,SubSortKey;
    ORA-01791: not a SELECTed expression
    It is one way that we use "group by".
    for instance
    select Val1,Val2
    from SortWithDistinct
    group by Val1,Val2
    order by max(sortKey),max(SubSortKey);
    Val1  Val2
    ---- ----
    1 3
    5 5
    4 6
    9 9
    3 5
    6 4
    7 3
    2 4
    It is one way that we use "Inline View".
    for instance

    select Val1,Val2
    from (select distinct Val1,Val2,sortKey,SubSortKey
    from SortWithDistinct)
    order by sortKey,SubSortKey;

    Furthermore, we may use below alternative solution which uses "dense_Rank".
    select Val1,Val2
    from (select distinct Val1,Val2,
    dense_Rank() over(order by sortKey,SubSortKey) as willSortKey
    from SortWithDistinct)
    order by willSortKey;
    Because "distinct" works after OLAP.
    for instance
    SQL> select distinct ColA,ColB,Row_Number() over(order by 1) as Rank
    2 from (select 1 as ColA,1 as ColB from dual
    3 union all select 1,1 from dual
    4 union all select 1,1 from dual
    5 union all select 1,1 from dual
    6 union all select 2,2 from dual
    7 union all select 2,2 from dual
    8 union all select 2,2 from dual)
    9 order by 1,2,3;
    ColA  ColB  Rank
    ---- ---- ----
    1 1 1
    1 1 2
    1 1 3
    1 1 4
    2 2 5
    2 2 6
    2 2 7
    my site :-)
    http://www.geocities.jp/oraclesqlpuzzle/1-6.html
This discussion has been closed.