SELECT all columns from Grouped By UNION query

[Iamthestig]

Hi, I'm having trouble returning all the columns from a Grouped By Union query in SQL 2014.

I'm trying to return the most recent search closed by company. I should only return 2 records. If I add the extra columns in the Group By clause I get extra unwanted rows.

SELECT 
	               lvl1.CompanyName,
                       MAX(CAST(jf.JOB_SEARCHCLOSED as date)) 'SearchClosed'
		       --jf.JOB_CONS1,
		       --jf.JOB_NUMBER,
		       --jf.JOB_STATUS,
		       --jf.JOB_NAME

               FROM
		tblOffLimits AS ol
		INNER JOIN
                   tblLevel1Companies AS lvl1
		     ON ol.Level1CompanyID = lvl1.CompanyID
                   INNER JOIN tblJobFile jf 
                     ON lvl1.CompanyCode = jf.JOB_COMPANYCODE
					 WHERE (ol.StartFrom = 'Completion')
					AND DATEADD(month,CAST(ol.Months AS int),jf.JOB_SEARCHCLOSED) < CAST(CURRENT_TIMESTAMP AS DATE)
					
					GROUP BY lvl1.CompanyName

UNION


SELECT 
                   tlvl1.CompanyName,
		   MAX(CAST(jf.JOB_SEARCHCLOSED as date)) 'SearchClosed'
		  --jf.JOB_CONS1,
                  --jf.JOB_NUMBER,
		  --jf.JOB_STATUS,
		  --jf.JOB_NAME
				  
               FROM
                   tblOffLimits AS ol JOIN
                         tblLevel2Companies AS tlvl2 
                     ON ol.Level2CompanyID = tlvl2.COMPANY_ID2
			JOIN tblLevel1Companies AS tlvl1
			  ON tlvl2.Company_ID = tlvl1.COMPANYID
                            JOIN tblJobFile AS jf ON tlvl1.CompanyCode = jf.JOB_COMPANYCODE
		WHERE ol.StartFrom = 'Completion'
		AND jf.JOB_STATUS = 'C'
		AND DATEADD(month,CAST(ol.Months AS int),jf.JOB_SEARCHCLOSED) < CAST(CURRENT_TIMESTAMP AS DATE)

GROUP BY tlvl1.CompanyName

This returns the 2 most recently Closed Grouped Company Names I want.

How can I return all the other columns that are commented out?

Any guidance greatly appreciated.

 

[Andrzejek]

Try:

 SELECT [blue]
       lvl1.CompanyName,[/blue]
       MAX(CAST(jf.JOB_SEARCHCLOSED as date)) 'SearchClosed',[blue]
       jf.JOB_CONS1,
       jf.JOB_NUMBER,
       jf.JOB_STATUS,
       jf.JOB_NAME[/blue]
FROM
...
GROUP BY [blue]lvl1.CompanyName,
       jf.JOB_CONS1,
       jf.JOB_NUMBER,
       jf.JOB_STATUS,
       jf.JOB_NAME[/blue]

Notice: all [blue]BLUE[/blue] fields in Select part (that are NOT MAX() ) are also in Group By part.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Olaf Doschke]

Most probably that just creates smaller groud, it works, but doesn't give the sums.

Let's look at it from the perspective of somy "anonymous" data

f1, f2, f3
----------
1, 1, 1
1, 2, 1
1, 3, 4
2, 2, 1
2, 1, 2

SELECT f1, sum(f2), f3 FROM table GROUP BY f1

Two parts of the result are clear, you get:

f1, sum(f2), f3
---------------
1, 6, ?
2, 3, ?

What should the database pick as value of f3? In the group f1=1 the values available in f3 are 1 and 4, in the group f1=2 the available values of f3 are 1 and 2.
Schrödingers Cat here, what should f3 be? 1 or 4? 1 or 2? dead or alive? Both at the same time?

SQL rejects such queries, as it doesn't make sense to have an undefined state.

If you know for sure the other columns are always a single value within the group of records you aggregate, you can do as Andy suggest or you can take MIN() or MAX(), it wouldn't matter. In this case it would. It would work, as you then also aggregate f3.

Bye, Olaf

 

[Iamthestig]

Thanks for your help. That's a very clear explanation Olaf.

I have solved it by using Partition By:

SELECT a.CompanyName, a.SearchClosed, a.JOB_CONS1, a.JOB_STATUS, a.JOB_NUMBER, a.JOB_NAME  FROM

	(

SELECT 
	               lvl1.CompanyName,
                       MAX(CAST(jf.JOB_SEARCHCLOSED as date)) 'SearchClosed',
		       jf.JOB_CONS1,
		       jf.JOB_NUMBER,
		       jf.JOB_STATUS,
		       jf.JOB_NAME,
			   row_number() over (partition by lvl1.CompanyName order by jf.JOB_SEARCHCLOSED desc) as rn

               FROM
		tblOffLimits AS ol
		INNER JOIN
                   tblLevel1Companies AS lvl1
		     ON ol.Level1CompanyID = lvl1.CompanyID
                   INNER JOIN tblJobFile jf 
                     ON lvl1.CompanyCode = jf.JOB_COMPANYCODE
					 WHERE (ol.StartFrom = 'Completion')
					AND DATEADD(month,CAST(ol.Months AS int),jf.JOB_SEARCHCLOSED) < CAST(CURRENT_TIMESTAMP AS DATE)
					
					GROUP BY lvl1.CompanyName,
					jf.JOB_SEARCHCLOSED,
					jf.JOB_CONS1,
					jf.JOB_NUMBER,
					jf.JOB_STATUS,
					jf.JOB_NAME 

UNION


SELECT 
                   tlvl1.CompanyName,
		   MAX(CAST(jf.JOB_SEARCHCLOSED as date)) 'SearchClosed',
		  jf.JOB_CONS1,
          jf.JOB_NUMBER,
		  jf.JOB_STATUS,
		  jf.JOB_NAME,
		  row_number() over (partition by tlvl1.CompanyName order by jf.JOB_SEARCHCLOSED desc) as rn
				  
               FROM
                   tblOffLimits AS ol JOIN
                         tblLevel2Companies AS tlvl2 
                     ON ol.Level2CompanyID = tlvl2.COMPANY_ID2
			JOIN tblLevel1Companies AS tlvl1
			  ON tlvl2.Company_ID = tlvl1.COMPANYID
                            JOIN tblJobFile AS jf ON tlvl1.CompanyCode = jf.JOB_COMPANYCODE
		WHERE ol.StartFrom = 'Completion'
		AND jf.JOB_STATUS = 'C'
		AND DATEADD(month,CAST(ol.Months AS int),jf.JOB_SEARCHCLOSED) < CAST(CURRENT_TIMESTAMP AS DATE)

					GROUP BY tlvl1.CompanyName,
					jf.JOB_SEARCHCLOSED,
				    jf.JOB_CONS1,
				    jf.JOB_NUMBER,
				    jf.JOB_STATUS,
				    jf.JOB_NAME 



) as a

WHERE a.rn = 1

Thanks again.