First n rows

From: Ivica Dimjasevic <remove_smokesoft_at_email.hinet.hr>
Date: Tue, 12 Mar 2002 18:42:49 +0100
Message-ID: <a6lev1$53sj$1_at_as201.hinet.hr>

Hi!

I have two tables on Oracle 8.0.4:

Table1:
id varchar2(10),
name varchar2(30)

Table2:
id varchar2(10),
value1 number(15,2),
value2 number(15,2),
...

Query like this:

SELECT DECODE(SIGN(s.sum), -1, 'P', 'D') AS dp,

             s.sum12,
             p.id
  FROM table2 p,
            (SELECT id,
                          SUM(value1 - value2) AS sum12,
                FROM table1
                WHERE ...
                GROUP BY id) s

  WHERE p.id = s.id
  ORDER BY dp, ABS(s.sum12) DESC

returns:

DP SUM12 ID

---

D      92121,11     323
D       2222,23      43
D           33,10      9876
D             1,99      3445


...

P     -43567,34      98843
P      -5544,33       91
P         -90,22       12
P           -5,99       90

....

This is OK, I get sum12 values, first pozitive then negative, ordered by size.

But, how to get only first two records having DP = 'D' and only first two records having DP = 'P'?
I want result like this:

DP SUM12 ID

---

D      92121,11     323
D       2222,23      43
P     -43567,34      98843
P      -5544,33       91

Thanks.

--
Ivica Dimjasevic
remove_smokesoft_at_email.hinet.hr

Received on Tue Mar 12 2002 - 18:42:49 CET