SubQuery... 1

[daviegravy]

I need some help refining my query...

My query is..

SELECT     dbo.DataEnrichmentRequirements.IEPOrderNumber, MAX(dcuser.RunBooks.ID) AS SpecialID, dcuser.RunBooks.RunBookName, 
                      dbo.Client_overview.wfg_flowsheet, dbo.Client_overview.wfg_flow, dbo.DataEnrichmentRequirements.IBEEnvironmentProd, 
                      dcuser.RunBooks.RunBookID
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, dcuser.RunBooks.RunBookID

Now my problem is I'm getting multiple records with the same OrderNumber and different SpecialID's. I need to refine that down to just getting the record with the MAX(SpecialID) for any particular RunbookID.

My results looks like...

OrderNumber   SpecialID    RunbookID  Other Data....
10001             45          1067
10001            300          1067 
10001           3450          1067 
10001           5000          1067

I need just the last record returned. Can anyone help me subquery to get the desired results? Thank so much!

 

[hmckillop]

does this work?

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

"I'm living so far beyond my income that we may almost be said to be living apart

 

[daviegravy]

Okay... I've tried modifying my query to meet your structure and I'm getting a syntax error around keyword "HAVING"

Here's my query...

SELECT     dbo.DataEnrichmentRequirements.IEPOrderNumber, 
           dcuser.RunBooks.ID,
           dcuser.RunBooks.RunBookName, 
           dbo.Client_overview.wfg_flowsheet, 
           dbo.Client_overview.wfg_flow, 
           dbo.DataEnrichmentRequirements.IBEEnvironmentProd
FROM       
          
       (SELECT 
           dbo.DataEnrichmentRequirements.IEPOrderNumber, 
           MAX(dcuser.RunBooks.ID) AS SpecialID,
           dcuser.RunBooks.RunBookName, 
           dbo.Client_overview.wfg_flowsheet, 
           dbo.Client_overview.wfg_flow, 
           dbo.DataEnrichmentRequirements.IBEEnvironmentProd,
           dcuser.RunBooks.ID
       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,
           dcuser.RunBooks.ID
       )
       HAVING dcuser.RunBooks.ID = MAX(dcuser.RunBooks.ID)

This is frustrating!! I appreciate the replies and any help I can get. Thanks! Just for lack of confusion the SpecialID is the same as dcuser.RunBooks.ID, I just made it different so people would understand that dcuser.RunBooks.ID, and dcuser.RunBooks.RunBookID are two different fields.

 

[SQLBill]

Notice that hmckillop's solution includes an alias for the derived table. You can see it just before the HAVING. The tb1 is needed for the derived table. Try adding that.

-SQLBill

Posting advice: FAQ481-4875

 

[daviegravy]

Okay,

I was wondering about that. I'm pulling from multiple tables though, ... Do I just list them normally like a FROM clause? His query shows that all those intial fields I select are from tb1 when they actually come from multiple tables.

 

[SQLBill]

If your FROM uses the results from a Sub-Select as a 'table', then you need to alias the table. If you are using 'real' tables then you shouldn't need to alias it. You DO need to identify columns where they can be in two or more other tables. Lets say you have a column CustomerID in tableA and tableB. Then you will need to identify which column is being used: either tableA.CustomerID or tableB.CustomerID.

What hmckillop solution does is to retrieve a large portion of the data (the FROM (SELECT ....)). He then uses that data as a 'derived table' (which needs an alias - tb1). From that subset of data, he then does the final select of data based on it meeting the 'HAVING' clause requirement. So, it's really two selects being done. First you get a lot of data, then the second select narrows the data down even more.

-SQLBill


Posting advice: FAQ481-4875

 

[hmckillop]

Thanks SQLBill, hadnt the chance to get back on to today, couldnt have explained it better,
Cheers.

"I'm living so far beyond my income that we may almost be said to be living apart