Problem with a query using MAX(ID)

I have a query regarding and I think I need to use max ID to get round it.I am creating a query and I need to get the Home Telephone number (from table 3).The problem is that there may be more than one home telephone nuber so I want to get the lastest one (highest ID).

I need to display all the person id from table along with their home no even if it is null.I’m not sure how to get round it whether I do a subquery joining the tables or whether I do a select case.

Please help

Table 1

Person ID

111

112

113

114

207

Table 2

PersonIdID

1110122

1110123

1120124

1130125

2070126

2070127

Table 3

IDTel_noType

01220125 23223Hone

01230122 43533Home

01240122 444111Mobile

0125077747474Mobile

012601222747474Home

012701232484848Home

Result

Person IDHome No

1110122 43533

112NULL

113NULL

114NULL

20701232 484848

use the following query...

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

X.PersonId

,Tel_No

,Y.ID

,Type Into #Temp

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select Home.PersonId,Home_No,Mobile_No from

(

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Home

Join

(

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Mobile

On Home.PersonId=Mobile.PersonId

|||Here are two versions, one for SQL Server 2005 and later, and one for SQL Server 2000 and earlier:

-- Requires SQL Server 2005 or later
with TPRanked as (
select
T1.PersonId,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

-- SQL Server 2000 or 7.0
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and T2a.ID > T2.ID
)

Steve Kass
Drew University
http://www.stevekass.com
|||I have just tried the above query in SQL but the problem is that it will show a NULL value if a mobile no has a higher ID than a Home no.|||

I need to do the query without creating any temporary tables.

|||

Ok.. here it is..

Note: If you use temp table then you can increase the performance

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) as D Group By PersonId) A

Left Outer Join (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) B On A.PersonId=B.PersonId And A.id=B.ID

|||I think I have a corrected query for SQL Server 2005. I changed the ranking criteria so that 'Home' numbers are always ranked highest. I'm working on the 2000 query. Obviously this needs careful testing!

with TPRanked as (
select
T1.PersonId, T2.ID,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by
case when T3.Type = 'Home' then 0 else 1 end,
T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

SK
|||Here's a correction for SQL 2000 along with another query that takes a different approach. The different approach doesn't generalize very well, but it's much more concise.

-- Quick and dirty
select
T1.PersonId,
(
select top (1) Tel_no
from #table2 as T2
join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where T2.PersonId = T1.PersonId
order by T3.ID desc
) as Tel_no
from #table1 as T1

-- Second try, first approach
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'

where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and (
(T3a.Type = 'Home' and (T3.Type is null or T2a.ID > T2.ID)) or
(T3.Type is null and T2a.ID > T2.ID)
)
)

SK