Select query Max date of Approved records

[bordway]

Hi,
Can you help me with this one?

I need to select Revision a parts
- from Approved (True) Revisions only
- the revision with the MAX Effective date

So from these part revisions
PartNum___Rev___Approved___EffectiveDate
1234567...C.....True.......1989-07-16
1234567...B.....True.......1990-07-16
1234567...A.....False......2008-11-30

I just want this one
PartNum___Rev___Approved___EffectiveDate
1234567...B.....True.......1990-07-16

I had previously used the SELECT statement below to get the Revision with the MAX date.
Now I need to consider the Approved state & have been stuck.

SELECT
pr01.partnum as "Part",
pr01.RevisionNum as "Rev",
pr01.Approved as "Aprv",
pr01.EffectiveDate as "Eff"
FROM PartRev pr01
LEFT PartRev pr02
ON (pr01.Company = pr02.Company and pr01.PartNum = pr02.PartNum and pr01.EffectiveDate < pr02.EffectiveDate)
WHERE pr01.PartNum = '1234567' and pr02.EffectiveDate IS NULL;

Haven't had any luck with adding any Approved criteria to the existing select statement.
I'm thinking I need scrap it and start over?

Thanks




Bruce O

 

[gmmastros]

If you truly want just one row in your output, the simplest thing is to use the TOP clause, like this:

SELECT   [!]Top 1[/!]
         partnum as "Part",
         RevisionNum as "Rev",
         Approved as "Aprv",
         EffectiveDate as "Eff"
FROM     PartRev
WHERE    PartNum = '1234567'
         And Approved = 'True'
ORDER BY EffectiveDate DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bordway]

>>Top 1
Yes, the one row is all I need for what I'm doing.
Thanks for the suggestion

 

[bordway]

F.Y.I.
It turned out I really needed a view and the ability to return multiple records.
I couldn't figure out how to get it to work in just one statement.
So ended up making one view & calling it in the other as follows.
It works but I'd be interested in suggested alternatives


-- Sub View
CREATE VIEW ApprovedRev AS
SELECT
pr.PartNum AS 'PartNum',
pr.RevisionNum AS 'Revision',
pr.Approved AS 'Approved',
pr.EffectiveDate AS 'EffectiveDate',
FROM PartRev pr
WHERE pr.Approved > 0

-- Final View
CREATE VIEW LatestApprovedRev AS
SELECT
pa.partnum as "Part",
pa.PartDescription as 'Desc',
pa.InActive as "InActive",
pr01.Revision as "Rev",
pr01.Approved as "Aprv",
pr01.EffectiveDate as "Eff",
FROM Part pa
JOIN ODBC.ApprovedRev pr01 ON pp.PartNum = pr01.PartNum
LEFT JOIN ODBC.ApprovedRev pr02
ON (pr01.PartNum = pr02.PartNum and pr01.EffectiveDate < pr02.EffectiveDate)
WHERE pr02.EffectiveDate IS NULL;

Bruce O

 

[gmmastros]

the ability to return multiple records

I assume you want to return 1 row per part number. If this assumption is wrong, the following query will not work without modifications.

; With Data As
(
  SELECT   partnum as "Part",
           RevisionNum as "Rev",
           Approved as "Aprv",
           EffectiveDate as "Eff",
           Row_Number() Over (Partition By partnum Order By EffectiveDate DESC) As RowId
  FROM     PartRev
  WHERE    Approved = 'True'
)
Select Part,
       Rev,
       Aprv,
       Eff
From   Data
Where  RowId = 1
Order By Part

The important part here is the Row_Number stuff. This basically assigns an incrementing counter to each row. The counter will reset at when when the partnum changes (this is the partition by part). The order of the counter is determined by the order by in the row number statement so that the latest effective date will appear at the top (and be numbered 1).

SQL server will not let you directly filter on the output of the row number column, so you need to use a common table expression (or derived table) so that you can apply the filter (Where rowid = 1).

I can almost guarantee this will perform better than nested queries.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bordway]

>>The important part here is the Row_Number stuff.
Thanks for the reply, good to know.

Unfortunately not an option in this case.
I'm working with an old version of OpenEdge 10.1B via ODBC
>>> the ROW_NUMBER function is not supported in OpenEdge and there is currently no equivalent function available.




Bruce O

 

[jbenson001]

This is a SQL Server forum... check to see if there is one for Open Edge

 

[bordway]

>>This is a SQL Server forum... check to see if there is one for Open Edge
OK thanks.

I forgot this was the ODBC connection to a legacy Progress version - SQL but limited.

I will test Row_Number on the newer versions
All MS SQL & they need the same updates.




Bruce O