Subquery or not?

[daviegravy]

I need help with my query.

SELECT     dbo.DataEnrichmentRequirements.IEPOrderNumber, MAX(dcuser.RunBooks.ID) AS IDField, dcuser.RunBooks.RunBookName, dbo.Client_overview.wfg_flowsheet, dbo.Client_overview.wfg_flow, dbo.DataEnrichmentRequirements.IBEEnvironmentProd
FROM dcuser.RunBooks INNER JOIN dbo.Client_overview ON dcuser.RunBooks.OverviewID = dbo.Client_overview.OverviewID INNER JOIN dbo.DataEnrichmentRequirements ON dcuser.RunBooks.RunBookID = dbo.DataEnrichmentRequirements.RunbookId
WHERE     (dbo.DataEnrichmentRequirements.IEPOrderNumber IS NOT NULL) AND (dbo.DataEnrichmentRequirements.IBEEnvironmentProd = 'on')
 
GROUP BY dbo.DataEnrichmentRequirements.IEPOrderNumber, dbo.Client_overview.wfg_flowsheet, dcuser.RunBooks.RunBookName, 
                      dbo.Client_overview.wfg_flow, dbo.DataEnrichmentRequirements.IBEEnvironmentProd

I have records that have the same IEPOrderNumber but different ID's. I want to retrieve just records where ID is the MAX of all those ID's per IEPOrderNumber. Can anyone help me get my desired results? Thanks so much!

 

[r937]

are you sure it isn't max(ID) per dbo.DataEnrichmentRequirements.RunbookId instead of max(ID) per dbo.DataEnrichmentRequirements.IEPOrderNumber?

select DE.IEPOrderNumber
     , Rmax.ID AS IDField
     , Rmax.RunBookName
     , C.wfg_flowsheet
     , C.wfg_flow
     , DE.IBEEnvironmentProd
  from dbo.DataEnrichmentRequirements as DE
inner
  join dcuser.RunBooks as Rmax
    on Rmax.RunBookID = DE.RunbookId
   and Rmax.ID
     = ( select max(ID)
           from dcuser.RunBooks
          where RunBookID = DE.RunbookId )
inner
  join dbo.Client_overview as C
    on C.OverviewID = Rmax.OverviewID
 where DE.IEPOrderNumber is not null
   and DE.IBEEnvironmentProd = 'on'

p.s. i bet this is microsoft sql server, not mysql, right?

r937.com | rudy.ca

 

[daviegravy]

Well the Runbook ID is the field that has differnet values for different records, the higher the value, the more recent it was modified. That's why I want to max on it so I can get the most recent.

Example:

OrderNum     RunBkID    Other Data...
10001          75
10001         2275
10001         3000
10001         5000

So I'm wanting to just return the record associated with ID 5000. And yes, I'm sorry this is microsoft sql and not mysql. I was just looking for a query help forum. Sorry about that. Any ideas on how to extract just the record with max ID?

 

[johnwm]

You could try forum183 or you could use the Find a Forum search box at the top of the page.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[daviegravy]

Thank you! I will post in there.

 

[r937]

if you do post in the SQL Server forum, i'm going to ask you the same question over there --

are you sure it isn't max(ID) per dbo.DataEnrichmentRequirements.RunbookId instead of max(ID) per dbo.DataEnrichmentRequirements.IEPOrderNumber?


r937.com | rudy.ca