SQL & PL/SQL

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

Hello,

I have the following problem;

Using dense_rank I have ranked the trees in a plot by height.

I now need to filter the first three records per plot based on the rank of the trees, this is complicated by the fact that sometimes two or three of the tallest three trees are the same height.

The records I want to use for plot number 90001 are tree numbers 4, 5 and 3.

The records I want to use for plot number 31000 are tree numbers 4, 5 and 3 but instead the tree number 2 is included.

select distinct plot_number
     , tree_number
     , tree_height
     , dense_rank() over (partition by plot_number order by tree_height desc)ht_rank
  from tst_rank
  order by plot_number, ht_rank asc
;

PLOT_NUMBER TREE_NUMBER TREE_HEIGHT HT_RANK
----------- ----------- ----------- -------
      31000           4          10       1 
      31000           5          10       1 
      31000           3           3       2 
      31000           2           2       3 
      31000           1           1       4 
      90001           5          20       1 
      90001           4          10       2 
      90001           3           3       3 
      90001           2           2       4 
      90001           1           1       5 

 10 rows selected 

The dense rank function allows me to rank the trees but it has then served its purpose. The following query is an example of what I want to accomplish - but - I need to access the first 3 records after the ranking has been carried out, so the following query is wrong to use the ranking.

select plot_number
     , max(decode(ht_rank, '1', tree_height*100)) ht_1
     , max(decode(ht_rank, '2', tree_height*100)) ht_2
     , max(decode(ht_rank, '3', tree_height*100)) ht_3
from (select distinct plot_number
           , tree_number
           , tree_height
           , dense_rank() over (partition by plot_number order by tree_height desc)ht_rank
        from tst_rank
       order by plot_number, ht_rank asc
      )txt  
 group by plot_number
;

PLOT_NUMBER HT_1 HT_2 HT_3
----------- ---- ---- ----
      31000   10    3    2 
      90001   20   10    3 

But I want to see;

PLOT_NUMBER HT_1 HT_2 HT_3
----------- ---- ---- ----
      31000   10   10    3 
      90001   20   10    3 

Would anyone have some ideas of what I could do to accomplish this?

Cheers
Ben


Create script to replicate my problem;

-- drop table tst_rank;

CREATE TABLE TST_RANK
( PLOT_NUMBER NUMBER
, TREE_NUMBER NUMBER
, TREE_HEIGHT NUMBER
);

INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('31000','1', '1');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('31000','2', '2');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('31000','3', '3');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('31000','4', '10');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('31000','5', '10');

INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('90001','1', '1');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('90001','2', '2');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('90001','3', '3');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('90001','4', '10');
INSERT INTO TST_RANK (PLOT_NUMBER, TREE_NUMBER, TREE_HEIGHT) VALUES ('90001','5', '20');

COMMIT;

/*

select distinct plot_number
     , tree_number
     , tree_height
     , dense_rank() over (partition by plot_number order by tree_height desc)ht_rank
  from tst_rank
  order by plot_number, ht_rank asc
;

select plot_number
     , max(decode(ht_rank, '1', tree_height)) ht_1
     , max(decode(ht_rank, '2', tree_height)) ht_2
     , max(decode(ht_rank, '3', tree_height)) ht_3
from (select distinct plot_number
           , tree_number
           , tree_height
           , dense_rank() over (partition by plot_number order by tree_height desc)ht_rank
        from tst_rank
       order by plot_number, ht_rank asc
      )txt  
 group by plot_number
;

*/

Edited by: benton on Oct 9, 2012 2:45 PM