Re: a select data from a table

ANSWERS EMBEDDED... Jingyan <jingyan.xu_at_tst2000.com> wrote in message news:YWqO3.582$7d2.14652_at_nuq-read.news.verio.net...
> Hi,
> 1.my a SQL statement is below.
> select * from worker
> where rownum between 10 and 12
> /
> When I run it, no rows select. in fact, worker table has 14 rows.
> If I run it like this:
> select * from worker
> where rownum between 1 and 12
> it selects 12 rows. I want to know why?

ROWNUM IS ASSIGNED ONLY WHEN A ROW IS SELECTED, SO IT ALWAYS STARTS WITH 1. WHEN U SAY BETWEEN 10 AND 12, IT WILL NEVER BE TRUE, COS THE FIRST ROWNUM IS 1 AND CAN NEVER BE BETWEEN 10 AND 12,
THE NEXT ROW WILL AGAIN HAVE ITS ROWNUM AS 1 AND NOT 2... WHILE USING ROWNUM IN THE WHERE CLAUSE, U HAVE TO CHECK FOR ROWNUM LESS THAN A NUMBER...
> 2. how to write a select statement to list the five oldest workers from
the
> worker table.
> select * from (select * from worker order by age desc)
> where rownum < 6;
> above is my way, but it doesn't work on my compuer. please help me.
> Thanks in advance,
> Jingyan

TRY THIS... select * from WORKER a
where 5 >= (select count(AGE) from WORKER b where b.AGE>= a.AGE) ORDER BY AGE DESC; VIJI Received on Tue Oct 19 1999 - 10:29:32 CDT