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

problem when addin an extra field to a select

I have a select statement that works fine:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays
from
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 26 rows returned. I need to add an extra field to the select. The Reason field on the Absence table contains a code, the value for this code, which I need, is stored in the Choices table. The Choices table has KEYID and ITEMID as the Primary Key. Reason field links to the ITEMID field.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, (B.Name) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K, clockwise.choices B,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
AND B.KeyID = 'R%' AND B.ItemID = A.Reason
ORDER BY E.Surname ASC, A.StartDate

I get 1 row, the only person from the previous select who has an entry in the Choices table. I understand why.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, SLReason
FROM (SELECT (B.Name) SLReason
FROM clockwise.choices B,
clockwise.Absence A
WHERE B.KeyID = 'R%'
AND B.ItemID = A.Reason),
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 104 rows, each person from the first select now appears 4 times with 1 of 4 different reasons.

How do I get 26 rows with just the 1 person showing his reason and the 25 other's having blank reasons.

Thanks for any help.Have a look at the Join predicate. Rather than define you realtionship using a where clause use a join. The join can cater for the situation where the value is null.

Sorry, don't have time to examine id detail your SQL but will try to later on tonight, that is if no one else has come up with a better answer in the mean time.|||What you need is a LEFT JOIN.

Based on the SQL that you've posted, you appear to be using Oracle, but I can't tell which version. If your Oracle is current enough to support the SQL-92 syntax, I'd strongly suggest that you switch to it because it makes a lot of things easier than the SQL-89 syntax that you are using in this query.

If you need more help, please post your Oracle version and whatever question(s) you might have.

-PatP|||I'm using Oracle8i, since I posted I've been trying to use a join (left outer) with no success so far.

Thanks for the info, I'll keep trying.|||Got it working by totally ignoring the choices table. I was getting more comlicated than I needed, I just added a decode for the Reason field:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode(A.Cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays,
Decode(A.Reason, '!', 'Certified', '#', 'Hospitalised', '$', 'Uncertified', '%', 'Unwell',
'&', 'Throat Infection', Null) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I got the 26 rows with the correct value.

Thanks for the help,
Mark.