Re: how can I get the second highest data?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 8 Aug 2001 08:31:34 +0200
Message-ID: <9kqmc6$110$1@s1.read.news.oleane.net>

"Á¤¿µ¼®" <illudus2_at_kornet.net> a écrit dans le message news: 9kp2ma$hk4$1_at_news1.kornet.net...
> I have two table as follows.
>
> create table TableA (id int, customer varchar(10))
> create table TableB (id int, itemno int)
>
> insert into TableA values ( 1, 'aaaa')
> insert into TableA values ( 2, 'bbbb')
> insert into TableA values ( 3, 'cccc')
> insert into TableA values ( 4, 'dddd')
>
> insert into TableB values (1, 1001)
> insert into TableB values (1, 1003)
> insert into TableB values (1, 1010)
> insert into TableB values (2, 1003)
> insert into TableB values (3, 1004)
> insert into TableB values (3, 1010)
> insert into TableB values (3, 1011)
> insert into TableB values (3, 1020)
> insert into TableB values (4, 1001)
>
> I want the resultset to have the id, customer and the number of times the
> orderId is present in TableB.
>
> Question)
>
> What I want to have is the resultset which has the record of the person who
> has the second highest number of items in his order. It will look something
> like given below:
>
> Desired output is ...
>
> iId customer no_of_items
> -- -------- -----------
> 1 aaaa 3
>
> How to achieve the above result?
>
>

v815> select id, customer, nb
  2 from (select id, customer, nb, rownum num

  3        from (select a.id, a.customer, count(*) nb
  4              from tableB b, tableA a
  5              where b.id = a.id
  6              group by a.id, a.customer
  7              order by nb desc))

  8 where num=2
  9 /

        ID CUSTOMER NB
---------- ---------- ----------

         1 aaaa                3

1 row selected.

--
Have a nice day
Michel

Received on Wed Aug 08 2001 - 01:31:34 CDT