I've got a a table House and a table Person
A house is contains 1 or 2 persons
If a house contains 2 persons the first person must be the one
It gives me this query :
from Person as FirstPerson
join (select IDHouse,IDPerson,Name From Person) as SecondPerson on FirstPerson.IDHouse = Second.IDHouse
join (select IDFoyer,NumberOfPersons from House) as TheHouse on FirstPerson.IDFoyer = TheHouse.IDHouse
(TheHouse.NumberOfPersons = '2' and
(FirstPerson.Name < SecondPerson.Name and FirstPerson.IDPerson <> SecondPerson.IDPerson) or
(First.Name = SecondPerson.Name and FirstPerson.IDPerson > SecondPerson.IDPerson)) or
(TheHouse.NumberOfPersons = '1')
select min(FirstPersons.Name) FirstPerson,
from Persons FirstPersons
left outer join Persons SecondPersons
on FirstPersons.IDHouse = SecondPersons.IDHouse
and SecondPersons.Name > FirstPersons.Name
group by FirstPersons.HouseID
You will need to link in the house table if you want to include houses with no people in them.
create table house(id int)
create table person(id int,houseid int,pname varchar(10))
insert house values(1)
insert house values(2)
insert house values(3)
insert person values(1,1,'a')
insert person values(2,1,'b')
insert person values(3,2,'c')
insert person values(4,3,'d')
insert person values(5,3,'e')
select (select top 1 pname from person where houseid=h.id order by pname) as fperson
,isnull((select top 1 pname from person where houseid=h.id
and id not in (select top 1 id from person where houseid=h.id order by pname)),'nobody') as sperson
from house h
Exactly. A "house" is a thing and a "person" is a thing and a "person" occupies no more than one "house."
You don't want a number_of_persons field in the "house" table nor do you want any field in that table named "occupant_id."
A more generalized schema would have a table such as occupancy which lists house_id and person_id to reflect a "many to many" relationship between people and houses. This would also allow you to reflect the history of occupancy as a person moves. The question is, what does your application require. Outside of college textbooks, there are no graven slabs of stone being handed down from any mountainside...
I've been tricked, I've simplified too much the problem
I can't use (Select top 1), Group by or Min() because I have about 20 columns in the Select
But I've kept the idea of adding a clause on a Left Outer Join
Select First.LastName,First.FirstName,Second.ClientType,T heHouse.City,...
From Person as First
Left outer join (Select IDPerson,FirstName,LastName,IDHouse From Person) as Second
On First.IDHouse = Second.IDHouse and
(First.LastName + First.FirstName + First.IDPerson <
Second.LastName + Second.FirstName + Second.IDPerson)
Join (Select IDHouse,City,NbPersons From House) as TheHouse
On First.IDHouse = TheHouse.IDHouse
(TheHouse.NbOfPersons = '2' and Second.IDPers is not null) Or
(TheHouse.NbOfPersons = '1')
But it seems that replacing the <Inner Join> by a <Left outer join>
to select the second person gives me slower stats
Placing a composite index on the LastName, FirstName, and IDPerson fields won't help because you are actually joining on the concatenated of these three strings. This is not a good idea, because not only is it slow and unable to take advantage of a composite index, but the sorting logic can give erroneous results if two people have similiar last names. For instance, someone named John Brown should come before someone name Abe Browning in an alphabetical sort by last name, but the reverse occurs when sorting on the concatenated strings "BrownJohn" and "BrowningAbe".
To be accurate you should sort and compare on the values separately.
P.S. Adding a comma between your concatenated fields may fix the problem in your logic: