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

wow tony thanks,
I also saw the rownum thing but when i tried the = or > operators they did just what u said "no rows selected" and i could figure it out but this definatley clears it up some. Just one more question if i needed say the last 20 how could i do this if i cannot order the rows firs tmy first thought was kinda like this but it did not work...

select *
from <whatever>
where (select * from <same> order by <col>) and colnum < 20

I tried to order it by the col i wanted in reverse order then get what would be the last 20 but i can not seem to get it to work anymore insight? By the way can you recommend some books or something the one I have is very limited! thanks you for the help so far

"Tony Hunt" <tonster_at_bigpond.net.au> wrote in message news:<4ORq7.77921$bY5.376372_at_news-server.bigpond.net.au>...
> 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 Sun Sep 23 2001 - 19:40:08 CEST