This discussion is archived
2 Replies Latest reply: Sep 27, 2012 3:04 PM by chris227 RSS

Analytical Functions in Group By

919241 Newbie
Currently Being Moderated
Hi

I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:

Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2

I split this out to values per year like this

Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2


Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?

I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?

Thanks

Edited by: 916238 on Sep 27, 2012 12:44 PM
  • 1. Re: Analytical Functions in Group By
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Whenever you have a problem, please post your existing query, along with a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples. I've read your message several times, and I can't figure out what you're asking, let alone how to do it. Sample data would really help.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
    916238 wrote:
    ... I tried a couple of different methods like Last_Value()
    That could be the answer: LAST_VALUE (... IGNORE NULLS ) to be precise.
    (Actually, FIRST_VALUE with DESCending order is usually easier, because you can use the default window.)
    but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?
    Analytic functions are computed after the GROUP BY clause is finished. Yes, the GROUP BY does restrict what you have available, but if a value is in your SELECT clause, then you can call an analytic function on it. I can show you exactly what I mean after I get the information in the first paragraph above.
  • 2. Re: Analytical Functions in Group By
    chris227 Guru
    Currently Being Moderated
    The testdata you gave are neither useful nor understandable.
    But if there are nulls in Value_2011 column, it is not surprising that you get null returned, because the defalut behaviour of order by is order by asc nulls last. If you ask for the last value, you will get it ;-)
    Adding nulls first is one way to get around that issue
    max(Value_2011) keep (dense_rank Last order by Month_ID nulls first)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points