Re: I need help on my new homework assignment it should be simpole for expieranced users

I found it! (Oracle specific...)

ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT *
    FROM emp
    WHERE ROWNUM < 10;

Note that conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM emp

    WHERE ROWNUM > 1; The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx

    SET col1 = ROWNUM;

Oracle assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index, so the ROWNUMs may be different than they would be without the ORDER BY clause.

"Zero_Cool" <tex___ass_at_hotmail.com> wrote in message news:417312e0.0109211248.13dd97c9_at_posting.google.com...
> thank you for your help but I still got a question is there any way to
> do this without a subquery?
>
>
>
>
> "Tony Hunt" <tonster_at_bigpond.net.au> wrote in message
 news:<QOEq7.76712$bY5.366010_at_news-server.bigpond.net.au>...
> > Something along the lines
> >
> > SELECT MAX(a.empid), MAX(b.empid)
> > FROM emp a, emp b
> > WHERE b.empip NOT IN (SELECT empid FROM emp GROUP BY salary HAVING
> > MAX(salary))
> >
> >
> > "Zero_Cool" <tex___ass_at_hotmail.com> wrote in message
> > news:417312e0.0109201236.465a916a_at_posting.google.com...
> > > hi all,
> > >
> > > ok here is my problem I need to get the two highest paid employees
> > > frolm a table
> > > without using a union I'm not sure but someone told me there is a
> > > function or something that will return a certain amount of rows from a
> > > group function like max(salary) but instead of getting just the max it
> > > would return like the top 2 or whatever is needed
> > > 1. Is there such a function?
> > > 2. If so what is the syntax?
> > > 3. Could you please leave me an example?
> > >
> > > man, I can not find anything like this anywhere any help would be
> > > appreciated and if it is not to much trouble could i ask someone to
> > > E-mail me anything realated
> > > thanx in advance
Received on Sat Sep 22 2001 - 03:27:28 CEST