Re: SELECT TOP N

Using a "rownum" assumes that there is some kind of ordering going on. Let's say
that we have a table like this:

create table sales_reps

(   rep_name     varchar(30)
,   sales        int

You can get a version of ranking by doing this:

select a.rep_name, count(*)
from sales_reps a, sales_reps b
where a.sales >= b.sales
group by a.rep_name
having count(*) = N

This is the most basic version, there are a bunch of issues that arise if
you have sales reps who are tied for total sales. Sybase and an outfit called
"Systar" used to offer a course called "Advanced T-SQL" which spent a few hours
dealing with these kinds of problems. (I don't know if they offer it any more.)

For another resource, search the 'net for "characteristic functions". You'll
find some pretty decent books on these kinds of tricks.

(Disclaimer: I would not like to use this query if I had 10,000 sales reps.)

proctorh_at_email.exide.com wrote:
>
> Oracle select range:
>
> select column
> from table
> where condition
> and rownum = n
>
> where n in the nth row number in the result set.
> you can also use <. >, <=, >=.
>
> I don't know if this is Oracle 8 compliant, though.
>
> HAP
>
> On 22 Jul 1998 06:18:48 GMT, paulkcng_at_news.netvigator.com (Ng K C
> Paul) wrote:
>
> >The identity() function only works in SYBASE. How about ORACLE and MSSQL
> >
> >Notus Spamus (spam_at_verio.com) wrote:
> >: select seq = identity(9), *
> >: into #tmp from tbl
> >: order by some_columns
> >: select *
> >: from #tmp
> >: where seq = @N
> >:
> >: Or expand to a range:
> >: where seq between @M and @N
> >:
> >: Ng K C Paul wrote:
> >: >
> >: > How about if I want the Nth record only and I want N is a parameter
> >: > passed to a stored procedure. Can I do that?
> >: >
Received on Fri Aug 14 1998 - 00:00:00 CDT