MAX() with Joins and Subqueries

I have the following tables:

TBL_DOMAIN domain_id domain_name

---------   ----------------------
1           Test
2           Production

TBL_DOC_TYPE doc_type_id doc_type_description

-----------   --------------------
1             MS Word
2             Image
3             XML File

TBL_DOC_NAMES

doc_id    doc_name           doc_type_id
------    --------------     -----------
1         payroll.doc        1
2         global.xml         3
3         company-logo.gif   2
4         mail_template.doc  1
5         emp.xml            3

TBL_DOC_VER doc_ver_id doc_id domain_id doc_ver_num doc_lastupdated_dt doc_lastupdated_user

---------- ------ --------- ----------- ------------------
--------------------
1          1      1         1           03-14-2002         mike
2          1      2         1           03-14-2002         cris
3          1      2         2           03-15-2002         jack
4          1      2         3           03-16-2002         tom
5          2      1         1           03-17-2002         mathew
6          2      2         1           03-18-2002         jane
7          3      2         1           03-19-2002         micheal
8          4      1         1           03-20-2002         shane
9          5      2         1           03-21-2002         bob
10         5      2         2           03-22-2002         kelly

Here, I want the details of latest version of the Document Records: 1) by its doc type
2) by its domain (Test/Production)

The query result should produce the following result.

doc_ver_id doc_type doc_name domain latest_ver last_date last_user

---------- ----------- ----------------  ------     ----------
----------  ---------
7          Image       company-logo.gif  production 1         
03-19-2002  micheal
1          MS Word     payroll.doc       test       1         
03-14-2002  mike
4          MS Word     payroll.doc       production 3         
03-16-2002  tom
8          MS Word     mail_template.doc test       1         
03-20-2002  shane
5          XML File    global.xml        test       1         
03-17-2002  mathew
6          XML File    global.xml        production 1         
03-18-2002  jane
9          XML File    emp.xml           production 2         

I tried various queries which includes joins and subqueries, but unable to get the above kind of result.
Can somebody help me?

Thanks in advance,

Kamlesh Received on Tue Jan 14 2003 - 06:47:53 CET