From: "Michel Cadot" Newsgroups: comp.databases.oracle.misc Subject: Re: how can I get the second highest data? Date: Wed, 8 Aug 2001 08:31:34 +0200 Organization: Guest of France Telecom Oleane's newsreading service Lines: 64 Message-ID: <9kqmc6$110$1@s1.read.news.oleane.net> References: <9kp2ma$hk4$1@news1.kornet.net> Reply-To: "Michel Cadot" NNTP-Posting-Host: 195.101.229.231 X-Trace: s1.read.news.oleane.net 997252295 1056 195.101.229.231 (8 Aug 2001 06:31:35 GMT) X-Complaints-To: abuse@oleane.net NNTP-Posting-Date: Wed, 8 Aug 2001 06:31:35 +0000 (UTC) X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 "Á¤¿µ¼®" a écrit dans le message news: 9kp2ma$hk4$1@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