Re: select x max values

From: Zbigniew Sliwa <zibi_at_at_hotmail.com>
Date: Fri, 25 Aug 2000 07:35:13 GMT
Message-ID: <Rkpp5.15061$Yl4.346147@news.tpnet.pl>

Faheem,

The mentioned solution will not work.

create table a (col1 number, col2 varchar2(1));

insert into a values(2,'b');
insert into a values(1,'c');
insert into a values(6,'f');
insert into a values(3,'i');
insert into a values(4,'a');
insert into a values(8,'d');

SQLWKS> select * from a;
COL1       C
---------- -
         2 b
         1 c
         6 f
         3 i
         4 a
         8 d

SQLWKS> SELECT col1 FROM a
     2> MINUS
     3> SELECT col1 FROM a
     4> WHERE ROWNUM+2 < (SELECT MAX(ROWNUM) FROM a)
     5> ORDER BY 1 DESC;

COL1

---

         8
         4
         3

Which is not OK.

Look in the explain plan for that statement. The sort is the last operation that SQL does for the second SQL statement in the union.

Miran,

Starting from 8.1.5 you can do:

SELECT col1

FROM     (SELECT col1
         FROM a
         ORDER BY col1 DESC)

WHERE rownum <= 3;

COL1

---

         8
         6
         4

Prior to 8.1.5 you can do:

SELECT a1.col1

FROM      a a1
WHERE     3 >=
          (SELECT   COUNT(*)
          FROM      a a2
          WHERE     a2.col1 >= a1.col1)

ORDER BY a1.col1 DESC;

COL1

---

         8
         6
         4

Which is slow and if you have a big table then I suggest using PLSQL.

DECLARE
CURSOR my_max IS
SELECT col1
FROM a
ORDER BY col1 DESC;
rec my_max%ROWTYPE;
counter NUMBER DEFAULT 0;

BEGIN
  OPEN my_max;
  FOR counter IN 1..3 LOOP
    FETCH my_max INTO rec;
    dbms_output.put_line(rec.col1);
  END LOOP;
  CLOSE my_max;
END; Processing ...
8
6
4
Statement processed

Good luck.

---

Regards,

Zbigniew Sliwa
Oracle Programmer
Poland
email: zibi_at_at_yahoo.com

faheemrao_at_my-deja.com napisa³(a) w wiadomo¶ci: <8o3b9n$kcd$1_at_nnrp1.deja.com>...
>
>
>Dear Miran
>
>I have got a solution of your problem
>
>
>try this query
>
>The following query assume that you have a table test_1
>having field value.
>
>SELECT VALUE FROM TEST_1
>MINUS
>SELECT VALUE FROM TEST_1
>WHERE ROWNUM+n < (SELECT MAX(ROWNUM) FROM TEST_1)
>ORDER BY VALUE DESC
>
>where n=(number of max records)-1
>
>Faheem
>
>In article <iR5p5.678$jL4.3596_at_news.siol.net>,
> "Miran" <aa_at_somone.si> wrote:
>> My problem:
>> I vant to select x max values from a table .
>> For example
>> My table is prices and it's values ar 10,11,12,13,14,15
>> I want to select 3 max values form this table, and I dont want it
 to be
>> any function or procedure just select. I have a solution but it's
 preate
>> slow, so if you have any idea ...
>>
>> miran
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Fri Aug 25 2000 - 02:35:13 CDT