Re: Help with a SQL query

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/12
Message-ID: <8i1b51$6s7$1@nnrp1.deja.com>#1/1

In article <8i14n4$2t4$1_at_nnrp1.deja.com>,   reddy <kspr_at_my-deja.com> wrote:
> Hi,
>
> I have a table called PolicyStmts and it has two columns called
> productTypeCode and Description. There could be
 multiple "Description"s
> for a particular productTypeCode. I would like to write a SINGLE query
> and get descriptions for a given set of product type codes and limit
> the number of descriptions for a particular productTypeCode to 10.
>
> I wrote the following SQL:
> select productTypeCode, Description
> from PolicyStmts
> where productTypeCode in (x,y,z)
>
> The above query returns all the descriptions for x,y and z. But I
 would
> like to have at most 10 descriptions for a give productTypeCode.
>
> Please help me out.
>
> Thanks in advance
> Reddy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If the set x,y,z is sufficiently small AND there are not thousands of entries per productTypeCode, the following should perform OK:

ops$tkyte_at_8i> create table policyStmts ( productTypeCode int, Description varchar2(25) );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> begin
  2      for i in 1 .. 4 loop
  3          for j in 1 .. 15 loop
  4              insert into policyStmts values ( i, 'Description ' ||
j );
  5          end loop;
  6      end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>
ops$tkyte_at_8i> commit;

Commit complete.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select productTypeCode, Description   2 from policyStmts a
  3 where productTypeCode in ( 1, 2, 3 )

  4     and 10 > ( select count(*)
  5                  from policyStmts b
  6                 where b.productTypeCode = a.productTypeCode
  7                   and b.rowid < a.rowid )

  8 /

PRODUCTTYPECODE DESCRIPTION

--------------- -------------------------
              1 Description 1
              1 Description 2
              1 Description 3
              1 Description 4
              1 Description 5
              1 Description 6
              1 Description 7
              1 Description 8
              1 Description 9
              1 Description 10
              2 Description 1
              2 Description 2
              2 Description 3
              2 Description 4
              2 Description 5
              2 Description 6
              2 Description 7
              2 Description 8
              2 Description 9
              2 Description 10
              3 Description 1
              3 Description 2
              3 Description 3
              3 Description 4
              3 Description 5
              3 Description 6
              3 Description 7
              3 Description 8
              3 Description 9
              3 Description 10

30 rows selected.

ops$tkyte_at_8i>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Jun 12 2000 - 00:00:00 CDT