asked on

SQL help

I am having problems with my query. What I need to do is to display additional columns when using MAX and subquery. I need to be able to only get the records that have the latest revision. Right now I can get this by using only doc id and max(rev) but I need other columns to tell me what project this belongs to, etc.

I need to add these columns dh.DhCreateDate, a.AdLabel, a.AdValue, p.PjName but I don't know how to do that without getting all the max values

With those additional fields I get:
DhDocID      Latest_Rev      DhCreateDate      AdLabel      AdValue      PjName
ED - 17928      1      2011-07-08 09:19:35.793      Job      519-15251      NULL
ED - 17928      2      2011-08-02 13:22:58.963      Job      519-15251      NULL
ED - 17928      3      2011-08-12 08:47:00.520      Job      519-15251 519-15251

But I need to only grab the latest revision which in this case is 3.

max2.sql

select  dh.DhDocID,  MAX( convert(varchar(10),dhrev)) as Latest_Rev, dh.DhCreateDate, a.AdLabel, a.AdValue, p.PjName

from Doc_Header dh INNER JOIN Attribute_Detail AS a ON dh.DhID = a.AdDhID LEFT OUTER JOIN
                      Project_Document_Relations AS pr ON pr.PdDhID = dh.DhID LEFT OUTER JOIN
                      Projects AS p ON p.PjID = pr.PdPjID 
where 
 (a.AdLabel LIKE '%job%')
 AND (dh.DhCreateDate > CONVERT(DATETIME, '2010-10-20 00:00:00', 102)) and dhdocid = 'ED - 17928'


group by DhDocID,dh.DhCreateDate, a.AdLabel, a.AdValue, p.PjName

Open in new window

Haver Ramirez

try this

SELECT * FROM (
select  dh.DhDocID,
		dhrev as Latest_Rev,
        dh.DhCreateDate,
        a.AdLabel,
        a.AdValue,
        p.PjName
        RANK() OVER (PARTITION BY dh.DhDocID,dhrev as Latest_Rev, dh.DhCreateDate, a.AdLabel, a.AdValue,p.PjName ORDER BY dhrev DESC) AS RNK 
from    Doc_Header dh
        INNER JOIN Attribute_Detail AS a ON dh.DhID = a.AdDhID
        LEFT OUTER JOIN Project_Document_Relations AS pr ON pr.PdDhID = dh.DhID
        LEFT OUTER JOIN Projects AS p ON p.PjID = pr.PdPjID
where   ( a.AdLabel LIKE '%job%' )
        AND ( dh.DhCreateDate > CONVERT(DATETIME, '2010-10-20 00:00:00', 102) )
        and dhdocid = 'ED - 17928'
group by DhDocID,
        dh.DhCreateDate,
        a.AdLabel,
        a.AdValue,
        p.PjName) derivate WHERE rnk = 1

Open in new window

Haver Ramirez

Ups, Sorry, without group

SELECT * FROM (
select  dh.DhDocID,
		dhrev as Latest_Rev,
        dh.DhCreateDate,
        a.AdLabel,
        a.AdValue,
        p.PjName
        RANK() OVER (PARTITION BY dh.DhDocID,dhrev as Latest_Rev, dh.DhCreateDate, a.AdLabel, a.AdValue,p.PjName ORDER BY dhrev DESC) AS RNK 
from    Doc_Header dh
        INNER JOIN Attribute_Detail AS a ON dh.DhID = a.AdDhID
        LEFT OUTER JOIN Project_Document_Relations AS pr ON pr.PdDhID = dh.DhID
        LEFT OUTER JOIN Projects AS p ON p.PjID = pr.PdPjID
where   ( a.AdLabel LIKE '%job%' )
        AND ( dh.DhCreateDate > CONVERT(DATETIME, '2010-10-20 00:00:00', 102) )
        and dhdocid = 'ED - 17928'
) derivate WHERE rnk = 1

Open in new window

NuclearLogistics

ASKER

this shows all three records

DhDocID      Latest_Rev      DhCreateDate      AdLabel      AdValue      PjName      RNK
ED - 17928      1      2011-07-08 09:19:35.793      Job      519-15251      NULL      1
ED - 17928      2      2011-08-02 13:22:58.963      Job      519-15251      NULL      1
ED - 17928      3      2011-08-12 08:47:00.520      Job      519-15251 519-15251      1

ASKER CERTIFIED SOLUTION

Haver Ramirez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial