Using MS SQL Server 2005...
Hopefully this is easy to solve. I have a stored procedure that I use as a datasource for a drop down list. This drop down list could contain a couple hundred items, so I provide the user with the ability to filter and sort the drop down list to help them find what they want to select. So I have one optional parameter that filters the list, and another that changes the sort order. Problem is, this query uses a join (1 to many), and my list comes up with duplicates. No problem, just apply the DISTINCT predicate. As soon as I do that, SQL Server complains that "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". I don't know what I need to specify in the select list because I'm using a case statement to order dynamically based on an input parameter.
Here is my stored procedure. Please advise!
ALTER PROCEDURE [dbo].[jwSiteMetaData]
@SiteID as int = null,
@SiteType as varchar(50) = null,
@SortOrder as varchar(20) = 'SD.SiteID'
SET NOCOUNT ON;
IF @SiteType = '' SET @SiteType = null
IF @SortOrder = 'SiteID' SET @SortOrder = 'SD.SiteID'
SELECT DISTINCT convert(varchar,SD.SiteID) + ' - ' + SiteName AS SiteIDSiteName, SD.SiteID, SiteName
FROM SiteDef AS SD LEFT JOIN SiteTypes AS ST ON SD.SiteID=ST.SiteID
WHERE SD.SiteID = coalesce(@SiteID,SD.SiteID)
AND ST.SiteType = coalesce(@SiteType, ST.SiteType)
WHEN @SortOrder = 'SiteName' THEN (RANK() OVER (ORDER BY SiteName))
ELSE (RANK() OVER (ORDER BY SD.SiteID))