0

I'm newbie in Oracle and I am programming some stuff things but I need to do something that it's beyond me.

First of all, I have to use Oracle 8i

We start with a table with data similar to:

Column1  Column2
      A        7
      A        9
      A       13
      B        5
      B        6
      C        1
      C        4
      C        9
      C       40

I need to take FIRST 2 values from this table for EACH letter (column1)

Value1 = n1 Value2 = n2

For example, for A values are 7 and 9 but for B are 5 and 6

Sorry, I can't draw a table (

And I don't know how many letter could have this table ofc, so I suppose the best way it's maybe a LOOP for all table, taking just first and second row (table is arranged and ALWAYS need first and second values for each letter) but I'm not sure how can I do...

Maybe a query filter by column1 and a rownum <3 ? I'm not sure if this query always take just first 2 values or take 2 aleatory values...

Julian lopez perez
  • 1
  • 1

1 Answers1

1

You can use a window function for this.

select column_1, column_2
from (
   select column_1, column_2,
          row_number() over (partition by column_1 order by column_2) as rn
   from the_table
) t
where rn <= 2;

You can find many more examples for this kind of problem under the tag greatest-n-per-group

Community
  • 1
  • 1
  • Thanks a lot, I forgot something important (now I'll edit post) I just can use oracle 8i (I dont know if I can use your code with 8i) – Julian lopez perez Jan 25 '16 at 08:21
  • @Julianlopezperez: that's one of the reasons you should always mention the (exact) version you are using. **Especially** if it is an completely outdated and unsupported version. Also "8i" isn't enough to identify your version. There are many different 8i releases (8.0.4, 8.0.5, 8.0.6, 8.1.5, 8.1.6, 8.1.7). Window functions were introduced with 8.1.6 –  Jan 25 '16 at 08:41
  • Sorry again, my bad. Version is 8.1.7 (I know, complety outdated but must to be in this version :( ) I tried query and appear to work, so thanks a lot :) I am reviewing windows funcion documentation too ofc :) – Julian lopez perez Jan 25 '16 at 09:02