[SQL Server 2000] - Selecting Nested Top n

[Colin_Infotheek]

Hi There,

We have problems getting the right records from a joined table.

First we have a table with all our Customers (eg. [Algemeen-B])
We join them with a table representing all Contacts per Customer (eg. [Algemeen-C])

An SP joins the tables because we have a few dynamic filters and parameters.

What we need is to link a maximum of @amount contacts per Customer.
Where each contact has a RANK (the TOP @amount ) joining the Customer Table.

We come thisfar:

-- The SP results this SQL select:

select *
from [Algemeen-B] left outer join
(
select * from [Algemeen-C] AS C
where exists
(
select * from [Algemeen-C]
group by bedrnummer
having bedrnummer=C.bedrnummer And min(rank) <= C.Rank and count(*) <= 2
) ) S
on [Algemeen-B].bedrnummer = S.bedrnummer
where KWP>=8

---
In this case I should return a maximum of 2 contacts per each Distinct Customer.
But I think the: 'And min(rank) <= C.Rank and count(*) <= 2' is wrong.

How can I select the TOP 2 Ranked Contacts for each Customer?
Because when there are no Contacts for the Customer it should return only the Columns for [Algemeen-B]
and NULL values for the Colums for [Algemeen-C].

If you need more info Tell me..
Hope someone can help me out..

Colin

[YuckFou]

drop table Contacts,Customers
create table Customers (cust_id int not null, cust_name varchar(30))
create table Contacts (cont_id int not null, cust_cust_id int not null, cont_name varchar(30))
alter table Customers add constraint pk_cust primary key (cust_id)
alter table Contacts add constraint pk_cont primary key (cont_id)
alter table Contacts add constraint fk_cust foreign key (cust_cust_id) references Customers (cust_id)
---
insert into Customers values (1,'aa')
insert into Customers values (2,'bb')
insert into Customers values (3,'cc')
insert into Contacts values (1,1,'ab')
insert into Contacts values (2,1,'ac')
insert into Contacts values (3,1,'ad')
insert into Contacts values (4,2,'bc')
---
select * from Customers cust
left outer join (
select x.* , n = (select count(*) from Contacts
where cust_cust_id = x.cust_cust_id and cont_id <= x.cont_id)
from Contacts x) cont
on cont.cust_cust_id = cust.cust_id and n <= 2
----

[YuckFou]

or if you have Contacts with PK (cust_cust_id,cont_id) where cont_id will number the contats for every customer
--
alter table Contacts add constraint pk_cont primary key (cust_cust_id,cont_id)
--
insert into Customers values (1,'aa')
insert into Customers values (2,'bb')
insert into Customers values (3,'cc')
insert into Contacts values (1,1,'ab')
insert into Contacts values (2,1,'ac')
insert into Contacts values (3,1,'ad')
insert into Contacts values (1,2,'bc') -- first contact for every customer always starts with 1
--
select * from Customers cust
left outer join Contacts cont
on cont.cust_cust_id = cust.cust_id and cont_id <= 2

[Colin_Infotheek]

Thanx for your fast reply !

I think your first answer will make everything work inside the SP.

The RANK we create is a bit difficult to explain because it's different with every Group (we have 4 groups each with different data)

Great to get good help


I'll let you know if we did not fix it.
(but I think this will work..)

Muchas Gracias.

Colin

[Colin_Infotheek]

YuckFou,

It's almost perfect.. (thanx Thisfar)

Let's see how to put this without useless text.

This SQL string is doin the business:
---
select * from [Algemeen-B] B
left outer join (
select X.*, n = ( select count(*)
from [Algemeen-C]
where bedrnummer = X.bedrnummer AND Rank <= X.Rank)
From [Algemeen-C] X) C
on C.bedrnummer = B.bedrnummer AND n <= 2
where KWP>=8 Order by C.bedrnummer
---

the interesting part of the output looks like this:

Name FCD RANK N |> n2
A MIC 2 3 | 2
B HIN 1 1 | 1
C IRO 500 4 | 4
D MIC 2 3 | 3
E NULL 1500 6 | 5
F NULL 1500 6 | 6

With 2 contacts we only get Name B in the output, (eg. second number is 3)
The N should be like 'N2' is counted for.
So an identifier for each record, and for double records(like A & D) also.

Is this possible?
We're this close now..

Colin

[YuckFou]

I'm not sure what you want to do, but if you'd like to have some unique identifier in your result, you can do it through some temptable.
Here is my example...
--- temp table with identity (this will count the records)
declare @result table (cust_id int , cust_name varchar(30),cont_id int , cont_name varchar(30) , i int identity(1,1))
---
insert into @result (cust_id,cust_name,cont_id,cont_name)
select cust_id,cust_name,cont_id,cont_name from Customers cust
left outer join (
select x.* , n = (select count(*) from Contacts
where cust_cust_id = x.cust_cust_id and cont_id <= x.cont_id)
from Contacts x) cont
on cont.cust_cust_id = cust.cust_id and n <= 2
order by cust_name , n
---
select * from @result

[Colin_Infotheek]

It's amazing how you can find the answer that fast.

- What kind of experience do you have with SQL server, and
- is it your full time job???

I'm gonna implement this in our SP and come back with the answer.
For now many thanx..
saves a lot of time (even think we did not find the answer ourselves anyhow)

So again many thanx.!

Colin