0

I need to select from two tables,

RATING_TABLE

  RATING_TYPE       RATING_PRIORITY
  TITAN             1
  PLATINUM(+)       1  
  PLATINUM          2
  DIAMOND(+)        3
  DIAMOND           3  
  GOLD              4
  SILVER            4

RATING_STORAGE

RATING           AMOUNT  
SILVER           200
GOLD             510
DIAMOND          850
PLATINUM(+)      980
TITAN            5000

I want to select the rating from RATING_STORAGE table based on RATING_PRIORITY from RATING_TABLE. I want to select one row with lowest rating priority. If two rating priority are eqaul I want to choose the one with the lowest amount. So I used the query,

select s.rating,s.amount 
from RATING_TABLE r, RATING_STORAGE s 
where        r.rating_type= s.rating_type 
and rownum=1 
order by  r.rating_priority asc , s.amount asc ;

I am getting correct output when sorting the result but rownum=1 fails to give the topmost row.

Thanks in Advance.

APC
  • 144,005
  • 19
  • 170
  • 281
Willwarasu Thiru
  • 21
  • 1

1 Answers1

1

You need to select after sorting is done, in your case:

     select *
  from (select s.rating
              ,s.amount
          from rating_table   r
              ,rating_storage s
         where r.rating_type = s.rating_type
           and rownum = 1
         order by r.rating_priority asc
                 ,s.amount          asc)
 where rownum = 1;
Rene
  • 10,391
  • 5
  • 33
  • 46
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • Thanks for the answer. I am using this query in stored procedure where I have to store the selected values with INTO clause which is making errors – Willwarasu Thiru Dec 06 '17 at 08:19