Skip to Main Content
  • Questions

  • Fetch the nth row from the last for a combination of records grouped based on certain columns

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, jenne.

Asked: April 27, 2012 - 12:12 am UTC

Last updated: May 02, 2012 - 1:49 pm UTC

Version: oracle 11g-11.2.0

Viewed 10K+ times! This question is

You Asked

Hi,
I am in need of ur urgent help.

We are grouping source records based on 3 fields.We want to fetch the third from the last for each group and populate it to target.We are struck with the logic on how to retrieve it.

Could you pls help us on this.
Advance thanks for your time.

and Tom said...

who us UR and why would you ask me about them?


no create :( no data, no example :(

I'll guess a little on this.

say you took all_objects and copied it into table t and have the query:

  6  select owner, object_type, object_name, count(*) cnt
  7    from t
  8   group by owner, object_type, object_name



that seems to match your description so far. Now what you want is the third to last record from each "group" - which I think means "break the data up by owner and object_type - sort by object_name - and within each owner/object_type - get the third to last record"

that is my guess - which I have to make since you provided no real details

So, we expand our query:

  3  select owner, object_type, object_name, cnt,
  4         row_number() over (partition by owner, object_type order by object_name DESC) rn
  5    from (
  6  select owner, object_type, object_name, count(*) cnt
  7    from t
  8   group by owner, object_type, object_name
  9         )



we broke the data up by owner, object_type and sorted backwards by object_name - assigning a number 1, 2, 3, 4, 5, ... to each row in the group - now we just:

ops$tkyte%ORA11GR2> select *
  2    from (
  3  select owner, object_type, object_name, cnt,
  4         row_number() over (partition by owner, object_type order by object_name DESC) rn
  5    from (
  6  select owner, object_type, object_name, count(*) cnt
  7    from t
  8   group by owner, object_type, object_name
  9         )
 10         )
 11   where rn = 3
 12  /

OWNER                          OBJECT_TYPE         OBJECT_NAME                           CNT         RN
------------------------------ ------------------- ------------------------------ ---------- ----------
APEX_030200                    FUNCTION            WWV_FLOW_ITEM_COMPS                     1          3
APEX_030200                    INDEX               WWV_MIG_SELECT_FILE_ID_IDX              1          3
APEX_030200                    PACKAGE             WWV_RENDER_CHART2                       1          3
...


and viola - we are done.


hope my guess was correct - if not, it should be close enough that you can get what you need by using the technique I demonstrated and adapting it to your needs.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Wish for analytics

Matthew McPeak, April 27, 2012 - 1:48 pm UTC 

Tom,

First question ... could you use DENSE_RANK and save an inline view level?  

<code>
SELECT *
FROM   (SELECT   owner,
                 object_type,
                 object_name,
                 COUNT (*) cnt,
                 DENSE_RANK ()
                 OVER (PARTITION BY owner, object_type
                       ORDER BY object_name DESC)
                    rn
        FROM     dba_objects
        GROUP BY owner, object_type, object_name)
WHERE  rn = 3


Second -- and what I really want to know...

I understand why you cannot use analytic functions in the WHERE clause. But do you think Oracle might ever introduce another SQL clause that would allow us to qualify records based on an analytic function. Maybe something like:

SELECT owner,
object_type,
object_name,
COUNT (*) cnt,
DENSE_RANK ()
OVER (PARTITION BY owner, object_type
ORDER BY object_name DESC)
rn
FROM dba_objects
-- *** New clause ***
WINDOWS HAVING DENSE_RANK ()
OVER (PARTITION BY owner, object_type
ORDER BY object_name DESC) = 3
-- *** END new clause ***
GROUP BY owner, object_type, object_name)

Since no one is really fully ANSI SQL compliant nowadays anyway... is Oracle at liberty to do things like that?</code>
Tom Kyte
April 28, 2012 - 8:14 am UTC

In this particular case, because we grouped by three columns and then partition by two of them and order by the third - we know that the ranking functions - dense_rank and rank - will actually do the same thing as row_number will

you could use rank or dense_rank in place of row_number *for this case* - but only because we know that object_name will be unique within owner and object_type because of the group by.

In general, rank and dense_rank *cannot* be used interchangeably with row_number.



I doubt we'd do the 'having' thing because it is part of grouping - and you do not group in general with analytics - we did so in this case because it was part of the stated requirement.

And we do have a viable way to reference the analytics in a where clause using with subquery factoring or inline views.

Not only for GROUPING queries...

Matthew McPeak, April 30, 2012 - 10:33 am UTC

Yes, sorry -- I should have been clear that the dense rank was only in this situation.

But about the new clause: it's not only for queries with a group by. I don't know about you, but I seem to write things like this (below) all the time:

SELECT object_type, object_name
FROM (SELECT object_type,
object_name,
ROW_NUMBER ()
OVER (PARTITION BY object_type ORDER BY object_name)
rn
FROM dba_objects)
WHERE rn <= 5


I'd much rather:

SELECT object_type,
object_name
FROM dba_objects
WINDOWS HAVING ROW_NUMBER () OVER (PARTITION BY object_type ORDER BY object_name) <= 5

But now that I think about it, I'm not sure how it would work because there could be many different windowing functions in the same query.

That's why they don't let me design databases I guess...

Thanks for your response, as always.

Matt

Tom Kyte
May 01, 2012 - 3:18 pm UTC

but - like I said - in general you are NOT grouping with analytics and having is specifically for grouping.


I write the inline view all of the time - probably more than you :) And frankly - I don't have a problem with it - in fact - I prefer to code inline views of inline views more and more often these days - to break the problem down into digestable pieces.


Matthews "WINDOWS HAVING" suggestion

Kim Berg Hansen, May 02, 2012 - 6:13 am UTC

Hi, Tom

I understand the wish of Matthew to be a third filtering clause like WHERE and HAVING. To be processed something like:

1) WHERE clause filters data
2) GROUP BY on the result of 1)
3) HAVING clause filters the result of 2)
4) Analytic functions are processed on the result of 3)
5) New "WINDOWS HAVING" clause filters the result of 4)

I don't think it would be easy to make, and I (like you, Tom) don't have a problem with inline views - I do them a lot myself (or a bunch of WITH clauses in complex cases.)

There is just one thing that sometimes makes me agree with Matthew - situations like this:

select column,column,..<long list>..,column
  from (
    select column,
           <expression> column_alias,
           ..<long list of columns/expressions/subqueries>..,
           (<subquery>) column_alias,
           row_number() over (partition by... order by...) rn
      from table join table join table join ... join table
  )
where rn <= 5;


A new "WINDOWS HAVING" would make it possible not to repeat the long column name list twice.

I know I can write:

select *
  from (
    select column, column,..<long list>..,column,
           row_number() over (partition by... order by...) rn
      from table join table join table join ... join table
  )
where rn <= 5;


But select * has drawbacks as well in production code ;-)

Anyway - cut-and-paste has been invented, so I can live with the inline views ;-)
Tom Kyte
May 02, 2012 - 1:49 pm UTC

I'm going to forward this on to the guys that code this stuff and see what they think about it.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.