HAVING MAX in SQL Server 2005

I am looking for an answer for the following question

I have a database containing 3 following tables:

TB-001_ISB
TB-002_ISDB
TI-003_ISBDB

The data under table TB-001_ISB are

ISB_No                  ISB_Ad
121XS9060301            ANKARA CANKAYA
122CV9061501            ANKARA YILDIZ
123BN9062081            ANKARA KIZILAY
124GH8709034            ANKARA MAMAK

The data under TB-002_ISDB are

ISDB_ID                ISDB_Sit
F-10-01-MM             Factory Production
F-20-01-MG             Factory Quality
F-30-01-FM             Site

The data under TI-003_ISBDB are

ISBDB_No              ISBDB_ISDB_ID   开发者_Go百科      ISBDB_Pers          ISBDB_Date
121XS9060301          F-10-01-MM            1234                01.01.2010
121XS9060301          F-20-01-MG            1234                02.01.2010
121XS9060301          F-30-01-FM            4321                03.01.2010
121XS9060301          F-20-01-MG            6785                04.01.2010
122CV9061501          F-10-01-MM            1234                01.03.2010
122CV9061501          F-20-01-MG            1234                02.03.2010
123BN9062081          F-20-01-MG            4321                03.10.2010
123BN9062081          F-30-01-FM            6785                04.10.2010

I need a query to get the following output:

ISBDB_No              ISB_Ad              ISDB_Sit         ISBDB_Pers          ISBDB_Date
121XS9060301          ANKARA CANKAYA      Factory Quality  6785                04.01.2010
122CV9061501          ANKARA YILDIZ       Factory Quality  1234                02.03.2010
123BN9062081          ANKARA KIZILAY      Site             6785                04.10.2010

So basically, I want the results to be sorted by ISBDB_Date and taking the max (ISBDB_Date) In order to do so, I wrote the following query

SELECT  [ISB_No], [ISB_Ad], [ISDB_Sit], [ISBDB_Pers], 
(SELECT (Max([ISBDB_Date]) 
FROM [TI-003_ISBDB]
GROUP BY [ISBDB_No])

FROM  (([TB-001_ISB] INNER JOIN [TI-003_ISBDB] ON [ISB_No]=[ISBDB_No]) 
INNER JOIN   [TB-002_ISDB] ON [ISDB_ID]=[ISBDB_ISDB_ID])

However, there are problems with this query. Can anybody help me? All of the efforts are highly appreciated...

---

Try something like this:

WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [ISBDB_No] ORDER BY [ISBDB_Date] DESC) Corr
    FROM [TI-003_ISBDB]
)
SELECT B.*, [ISB_Ad], [ISDB_Sit]
FROM [TB-001_ISB] A
INNER JOIN (SELECT * FROM CTE WHERE corr = 1) B
ON A.[ISB_No] = B.[ISBDB_No]
INNER JOIN [TB-002_ISDB] C
ON C.[ISDB_ID] = B.[ISBDB_ISDB_ID]

---

SELECT
    ISB_No
    ,ISB_Ad
    ,ISDB_Sit
    ,ISBDB_Pers
    ,ISBDB_Date
FROM  [TB-001_ISB] AS isb
INNER JOIN [TI-003_ISBDB] AS isdbd ON isb.ISB_No=isdbd.ISBDB_No 
INNER JOIN   [TB-002_ISDB] AS isdb ON isdb.ISDB_ID=isdbd.ISBDB_ISDB_ID
WHERE isdbd.ISBDB_Date = (select MAX(isdbd1.ISBDB_Date) FROM [TI-003_ISBDB] AS isdbd1
    WHERE isdbd.ISBDB_No = isdbd1.ISBDB_No)
ORDER BY isdbd.ISBDB_Date DESC