Query syntax

Posted on 2013-08-27
Medium Priority
Last Modified: 2013-08-30
I have three tables which i have joined in as below.

This query returns many rows of data and is nearly what i want.

However how would i only get the rows where dbo.Documents.doc_id is at its maxium?

SELECT        dbo.Applications.app_id, dbo.Applications.app_XMSApplicationID, dbo.Applications.app_XMSApplicationName, dbo.Applications.app_Active, 
                     dbo.DocumentData.ddt_FieldName, dbo.DocumentData.ddt_FieldValue, dbo.Documents.doc_id
FROM          dbo.Applications
                     INNER JOIN dbo.Documents ON dbo.Applications.app_XMSApplicationID = dbo.Documents.doc_ApplicationID
                     INNER JOIN dbo.DocumentData ON dbo.Documents.doc_id = dbo.DocumentData.doc_id
					 where  dbo.Applications.app_XMSApplicationID = 62

Open in new window

Question by:soozh
  • 2
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39441446
I'm not sure I understand what you mean by where dbo.Documents.doc_id is at its maximum. Do you want the records only where the Doc_ID is the largest value - in other words only one doc_id?

LVL 11

Assisted Solution

Louis01 earned 800 total points
ID: 39441650
Try this?
SELECT a.app_id
     , a.app_XMSApplicationID
     , a.app_XMSApplicationName
     , a.app_Active
     , dd.ddt_FieldName
     , dd.ddt_FieldValue
     , d.doc_id
  FROM dbo.Applications a
        INNER JOIN dbo.Documents d
    ON a.app_XMSApplicationID = d.doc_ApplicationID
        INNER JOIN dbo.DocumentData dd
    ON d.doc_id = dd.doc_id
   AND EXISTS (select d1.doc_ApplicationID, MAX(d1.doc_id) as max_doc_id
                 from dbo.Documents d1
                group by d1.doc_ApplicationID
                where d1.doc_ApplicationID = d.doc_ApplicationID
               having d.doc_id = MAX(d1.doc_id))
 WHERE a.app_XMSApplicationID = 62

Open in new window

LVL 49

Accepted Solution

PortletPaul earned 1200 total points
ID: 39441680
     , dbo.Applications.app_XMSApplicationID
     , dbo.Applications.app_XMSApplicationName
     , dbo.Applications.app_Active
     , dbo.DocumentData.ddt_FieldName
     , dbo.DocumentData.ddt_FieldValue
     , D.doc_id
FROM dbo.Applications
                  , max(doc_id) AS doc_id
             FROM dbo.Documents
             GROUP BY
             ) AS D ON dbo.Applications.app_XMSApplicationID = D.doc_ApplicationID
INNER JOIN dbo.DocumentData ON D.doc_id = dbo.DocumentData.doc_id
WHERE dbo.Applications.app_XMSApplicationID = 62

Open in new window

nb: if there is a possibility that a document does not exist for any dbo.Applications.app_XMSApplicationID then the inner joins may have to be left joins.
LVL 49

Expert Comment

ID: 39451669
Thanks! Cheers, Paul

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question