0

Hello I'am very new to writing SQL and I am trying to find the appropriate way to use TOP in Oracle SQl 9:

My example:

select * from example e, test t
where e.id = t.id
and country = 'USA'
order by state ASC;

What I am trying to do is take the bottom 20 % of my query but I know you cannot use TOP. After researching I still have not found an applicable answer. I know you have to first order them but am unsure of how to then take the bottom 20%(which would be TOP since the order is ASC)

APC
  • 144,005
  • 19
  • 170
  • 281
user2402107
  • 913
  • 5
  • 22
  • 43
  • 1
    If you are new to SQL, why are you using SQL9 and not, at least, SQL11? There may be country restrictions, but I believe the express edition (Oracle 11.2) is available for free in most places in the world. If you are in school, and the school uses SQL9, you should raise objections - they are stealing your money (if you are paying for the course) by teaching you a very old version of Oracle. –  Nov 27 '16 at 18:30
  • is there a technique used in 11.2 for this situation? – user2402107 Nov 27 '16 at 18:32
  • Not for your problem - the analytic functions needed for solutions existed in 9i already. But I would still ask the question on principle. There are many features that were added in Oracle 10 and more still in Oracle 11, and it seems they wouldn't be covered if all you learn is Oracle 9. –  Nov 27 '16 at 18:45

2 Answers2

1

In general (like if you want the top or bottom 17.2% of the rows) you can use row_number() and count() (analytic functions) to get the result.

20% is easier - you are looking for the top (or bottom) quintile. For this, you can use the ntile() function, like so:

select [column_names]
from (
       select e.*, t.*, ntile(5) over (order by state) as nt
       from .....  etc
     )
where nt = 1;

The subquery is your query. The column_names in the outer query are whatever you actually need; you could also use select * but that will show the ntile too (which will be 1 in all rows).

0

If sorting something in ASCending order gives us the top set then surely sorting in DESCending order can give us the bottom set.

This solution uses the function NTILE() to divide the records into five buckets. The first bucket is the set we want (because sorted in descending order). Sorting in ascending order and taking the fifth quintile would have the same outcome.

select * from (
    select e.*
           , t.*
           , ntile(5) over (order by state desc) nt 
    from example e, test t
    where e.id = t.id
    and country = 'USA'
)
where nt = 1 
order by state desc
/

You don't say what your sort criteria are, so I've guessed.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I believe the OP wanted the bottom 20%, so ordering ascending and taking the top 20% (first quintile) would be the desired result. It doesn't really matter when using `NTILE()` anyway, it is a matter of filtering by `nt=1` vs `nt=5`. The "sort" criterion was `state` (judging by the OP's attempt and plain-language description). Also, it seems the only purpose of the ORDER BY clause was to get close to a solution, so it is probably not needed in the complete solution. –  Nov 27 '16 at 18:44