How to get the first N rows from a sorted result set?

[mackpei]

Hi gurus,

I have this question, because the statement:

SELECT * FROM my_tab WHERE rownum <= 50
ORDER BY 1

doesn't always work, because I know:
Oracle7 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.

Any clues?

TIA
Mack

 

[bigscouse]

What do you mean it does not always work?
Can we have an example please?

 

[MalcolmW]

I'm not familiar with Oracle, but
Select *
From (select *
from sometable
order by 1) AS sortedtable
where rownum <= 50

might get around the problem, if Oracle assigns the rownumber as you say.

 

[mackpei]

Unfortunately Oracle (7) doesn't accept the &quot;AS sortedtable&quot; syntax.

And
Select *
From (select *
from sometable
order by 1)
where rownum <= 50

doesn't work in Oracle either.

 

[THoey]

I am running Oracle 7.3.4. I created a table called TEST and put a number field called TEST in it. I loaded five records into the table with the following values, in the following order (1,3,2,5,4).

If you just do a select *, you get the following sorted results:

1* select * from test;

TEST
---------
1
2
3
4
5

If you use the ROWNUM feature, they are not sorted:

1* select * from test where rownum < 4

TEST
---------
1
3
2


But I found that this works:

1 select * from test
2 where test in (
3 select test from test
4 where rownum < 4)
5* order by test

TEST
---------
1
2
3

Hope that helps...

 

[mackpei]

But if I get:

1* select * from test where rownum < 4

TEST
---------
4
3
2
It is possible (see my first posting), then I am afraid I'll get the following
result using your last statement:

TEST
---------
2
3
4

But my original expectation was:

TEST
---------
1
2
3

It seems that there's no way to get top N sorted rows using an SQL
statement. Perhaps we have to use a cursor in stored procedure ...

 

[MalcolmW]

Check out Terry's post again.
select * from test
where test in (
select test from test
where rownum < 4)
order by test

 

[mackpei]

No, it doesn't work as I required:

create table mack (test integer);
insert into mack values (5);
insert into mack values (4);
insert into mack values (3);
insert into mack values (2);
insert into mack values (1);

select * from mack;

TEST
==========
5
4
3
2
1

5 ROWS SELECTED

select * from mack
where test in (
select test from mack
where rownum < 4)
order by test;

TEST
==========
3
4
5

3 ROWS SELECTED

 

[THoey]

Mack is right, my SQL doesn't return the results he is looking for. What I understand he wants to do is:

select * from mack
where test in (
select test from mack
order by test)
where rownum < 4;

Order the records and then limit the number of rows returned. Unfortunately, Oracle dies on the order by statement when put this way.

Mack, if you get an answer that works, I would love to see it. Just in case I have a future need...

 

[mackpei]

Interesting ... how about this:

select a.test from mack a, mack b
where a.test = b.test and rownum < 4
order by a.test;

It seems to work (and only for ASC) !

Mack

 

[mackpei]

Here is an &quot;improved&quot; version for the table with duplicate values:

select test from
(select distinct a.test from mack a, mack b
where b.test = a.test)
where rownum < 4
order by test;

I noticed that the subquery (with join) already returns a sorted
(ascending) result set, and thus the &quot;order by&quot; is not necessary

So is this an Oracle feature or just a coincidence?

Mack

 

[MikeLacey]

Mack,

This comes up a lot -- how do you fancy turning this thread into a FAQ?