how can I get the second highest data?

From: Á¤¿µ¼® <illudus2_at_kornet.net>
Date: Wed, 8 Aug 2001 00:55:18 +0900
Message-ID: <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? Received on Tue Aug 07 2001 - 10:55:18 CDT