Problem using SELECT DISTINCT - 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 2000 Forums

SQL Server Development (2000)

Problem using SELECT DISTINCT

Author

Topic

MirandaJ
Starting Member

35 Posts

Posted - 2008-01-30 : 12:43:46

I am stumped as to why I am getting this message

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Does anyone see what I am missing?


CREATE PROCEDURE sp_AdminListApplicants

@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)

AS


IF @JobID <> 9999
BEGIN		
	IF @LocationID <> 9999
	BEGIN
		SELECT DISTINCT
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			AL.LocationID= @LocationID 
			AND A.FolderID= @FolderID
			AND A.JobID = @JobID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
	
	ELSE
	BEGIN
		SELECT DISTINCT
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			A.FolderID= @FolderID
			AND A.JobID = @JobID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
END
	
ELSE
BEGIN
	IF @LocationID <> 9999
	BEGIN
		SELECT DISTINCT
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation  
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			AL.LocationID= @LocationID 
			AND A.FolderID= @FolderID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
	
	ELSE
	BEGIN
		SELECT DISTINCT 
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			A.FolderID= @FolderID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
END
GO

Miranda

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-30 : 13:43:40

Yeah DISTINCT and CASE in ORDER BY dont go well together. You can get around it like this:

SELECT * from ( SELECT DISTINCT....) A ORDER BY CASE .....END, CASE ....END

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MirandaJ
Starting Member

35 Posts

Posted - 2008-01-30 : 14:27:56

So then one of the querys in the procedure would look like this?


SELECT * FROM ( 
	SELECT DISTINCT 
		A.UserID, 
		A.Completed, 
		A.FolderID, 
		A.AppDateTimeStart, 
		A.ResumeFileName, 
		A.FirstName, 
		A.LastName, 
		A.PrescreenScore, 
		A.JobID, 
		A.ViewPre, 
		A.ViewApp, 
		A.ViewReport, 
		A.ViewResume, 
		J.JobTitle,
		J.TestType,
		L.BranchAbbreviation
	FROM 
		Locations L 
		INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
		INNER JOIN Application A ON AL.UserID = A.UserID
		INNER JOIN Jobs J ON J.JobID = A.JobID  
	WHERE 
		A.FolderID= @FolderID
)
ORDER BY
	CASE
		WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
	END DESC,
	CASE
		WHEN @SortOrder = '6' THEN A.PreScreenScore 
	END DESC,
	CASE
		WHEN @SortOrder = '2' THEN A.LastName  
	END DESC,
	CASE
		WHEN @SortOrder = '5' THEN A.PreScreenScore 		
	END ASC,
	CASE
		WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
	END ASC,
	CASE
		WHEN @SortOrder = '1' THEN A.LastName 		
	END ASC

Miranda

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-30 : 16:23:05

Yes... have you tried it? Do the results match?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MirandaJ
Starting Member

35 Posts

Posted - 2008-01-30 : 16:58:55

I needed to make a few changes but that solved the issue thanks much

Miranda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources