SELECT GROUP BY Nightmare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT GROUP BY Nightmare

Author

Topic

hobbsieoz
Starting Member

2 Posts

Posted - 2011-07-14 : 09:30:41

This is the current query i have which selects records from a training records database. What i want it to do is show the most recent record for each employee for each different training module.
Because of the grouping by tblASSESSRESULT.DESCRIPTION it is returning a record for each person for each module where there are two different result, not each persons individual result.
So the question is, how can i get rid of the grouping by tblASSESSRESULT.DESCRIPTION without SQL giving me errors?

SELECT 
	fwatch.tblTRAINMODULE.DESCRIPTION AS MODULE,
	fwatch.tblEMPLOYEE.SURNAME,  
	MAX(fwatch.tblTRAINASSESS.ASSESS_DATE) AS ASSESS_DATE, 
	fwatch.tblASSESSRESULT.DESCRIPTION AS RESULT, 
	fwatch.tblTRAINMODULE.REPEAT_PERIOD
	
FROM fwatch.tblEMPLOYEE
CROSS JOIN fwatch.tblTRAINMODULE 
LEFT OUTER JOIN fwatch.tblTRAINASSESS ON fwatch.tblTRAINMODULE.ID = fwatch.tblTRAINASSESS.MODULE_ID  
LEFT OUTER JOIN fwatch.tblASSESSRESULT on fwatch.tblASSESSRESULT.ID = fwatch.tblTRAINASSESS.RESULT_ID 

GROUP BY  
	fwatch.tblTRAINMODULE.DESCRIPTION, 
	fwatch.tblEMPLOYEE.SURNAME,  
	fwatch.tblTRAINMODULE.REPEAT_PERIOD,
	fwatch.tblASSESSRESULT.DESCRIPTION
	
ORDER BY fwatch.tblTRAINMODULE.DESCRIPTION

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 12:44:50

By necessity, either you have to pick one of the descriptions, or assemble the multiple descriptions together to have a composite description. If you want to pick one of the descriptions, do:

SELECT 
	fwatch.tblTRAINMODULE.DESCRIPTION AS MODULE,
	fwatch.tblEMPLOYEE.SURNAME,  
	MAX(fwatch.tblTRAINASSESS.ASSESS_DATE) AS ASSESS_DATE, 
	MAX(fwatch.tblASSESSRESULT.DESCRIPTION) AS RESULT, 
	fwatch.tblTRAINMODULE.REPEAT_PERIOD
......

It will just pick the description that comes last in an alphabetical sort.

parody
Posting Yak Master

111 Posts

Posted - 2011-07-15 : 08:50:17

Would a CTE with a

rownumber() OVER (PARTITION BY fwatch.tblEMPLOYEE.SURNAME,fwatch.tblTRAINMODULE.DESCRIPTION ORDER BY Assess_Date DESC)

Then select row numbers of 1 from the CTE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources