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

Question

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

Miranda Johnson

Mr or Mrs. 500

Points: 523
More actions
January 30, 2008 at 9:29 am

#62736

Can anyone see what I am missing?
CREATE PROCEDURE sp_AdminListApplicants
@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder varchar(5)
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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are those distincts necessary?

I believe that when distinct is specified, the order by (in your case, the case statement) must appear in the select clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Miranda Johnson Mr or Mrs. 500 Points: 523 More actions · Answer 2

The Application table will only hold one record(The UserID in this case is the Primary Key on this table). However, the Application_Locations table may hold many records for each UserID. As it is a many to many table wirth two columns holding the UserID and LocationID the person is applying to. There is a record for each location that the user applied to. Since the applicant may apply to many different locations if I don't do the SELECT DISTINCT it will show many records and I only need one to display at this point. I wouldn't need the DISTINCT if @LocationID <> 9999 as the query would only grab the one record for the user. But if it does then I only want to display one. So two of the queries don't need the DISTINCT but the other two do.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then you can try something like this.

select col1, col2 from

(Select distinct col1, col2, col3 from sometable) subquery

order by case when @sortorder = 1 then col1 else col3 end asc

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

One other thing, I'd change your naming conventions for stored procedures. Every time you execute the procedure sp_AdminListApplicants, SQL Server will first look in the master database for the procedure since its name starts with sp_.

😎

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

I wouln't say this is efficient. But this is a way to do it.

Such situation I always sort in the front end. If you are using ado.net it is much easier to do it there.

DECLARE @myTable TABLE

(

MyDateDATETIME,

myStrVARCHAR(20)

)

INSERT @myTable

SELECT '01/01/2005', 'JJJ'UNION

SELECT '01/02/2005', 'III'UNION

SELECT '01/03/2005', 'HHH'UNION

SELECT '01/04/2005', 'GGG'UNION

SELECT '01/05/2005', 'FFF'UNION

SELECT '01/06/2005', 'EEE'UNION

SELECT '01/07/2005', 'DDD'UNION

SELECT '01/08/2005', 'CCC'UNION

SELECT '01/09/2005', 'BBB'UNION

SELECT '01/10/2005', 'AAA'

DECLARE @SortOrder CHAR(1)

SELECT @SortOrder = '3'

SELECT MyDate, myStr

FROM

(

SELECT DISTINCT MyDate, myStr

FROM

@myTable

) A

ORDER BY

CASE WHEN @SortOrder = '1' THEN MyDate END DESC,

CASE WHEN @SortOrder = '2' THEN MyDate END ASC,

CASE WHEN @SortOrder = '3' THEN myStr END DESC,

CASE WHEN @SortOrder = '4' THEN myStr END ASC

Regards,
gova

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

You beat me on that Gilamonster when I was typing the sample code.

BTW.

order by case when @sortorder = 1 then col1 else col3 end asc

will throw error if col1 and col3 are different datatypes.

Regards,
gova

Miranda Johnson Mr or Mrs. 500 Points: 523 More actions · Answer 7

we use dbo.sp_Whatever when we execute the sprocs. I was told this will make it not look at the master table is that incorrect?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

gova (1/30/2008)
You beat me on that Gilamonster when I was typing the sample code.

🙂 I'm feeling a bit lazy tonight, so very simple sample code

BTW.
order by case when @sortorder = 1 then col1 else col3 end asc
will throw error if col1 and col3 are different datatypes.

I think it will if col3 can't be implicitly converted to the type of col1. Not sure, it's been a while since I've done this kind of order by.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Miranda Johnson (1/30/2008)
we use dbo.sp_Whatever when we execute the sprocs. I was told this will make it not look at the master table is that incorrect?

It's incorrect. Any proc that starts sp_ or xp_ is checked for in the master database first. If no match is found, then the user database is checked.

It happens even if you fully clarify the proc's name - EXEC MyDB.dbo.sp_something -- will still check master first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Miranda Johnson Mr or Mrs. 500 Points: 523 More actions · Answer 10

Miranda Johnson

Mr or Mrs. 500

Points: 523

January 30, 2008 at 1:11 pm

#773499

Thanks Much You have been a great help 🙂

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 11

I would suggest to usp_ (user stored procedure) is you must use a prefix.

This is what BOL says

System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

The stored procedure in the master database.

The stored procedure based on any qualifiers provided (database name or owner).

The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Regards,
gova