Jahelka
asked on
SELECT DISTINCT w/Join, ORDER BY in CASE
Hello Experts,
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!
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'
AS
BEGIN
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)
ORDER BY
CASE
WHEN @SortOrder = 'SiteName' THEN (RANK() OVER (ORDER BY SiteName))
ELSE (RANK() OVER (ORDER BY SD.SiteID))
END
END
do this
if @SortOrder="..."
begin
select ...
end
if @SortOrder="..."
begin
select ...
end
use different simple statements instead of trying to order by complicated statements...
if @SortOrder="..."
begin
select ...
end
if @SortOrder="..."
begin
select ...
end
use different simple statements instead of trying to order by complicated statements...
Do what HainKurt suggested. Better both for readability and performance. (pls no poinks)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I hadn't thought to use a Group By as I usually only use those in conjunction with max() or min() or one of those guys. Nice, elegant solution!
HainKurt, wouldn't your solution be considered "dynamic sql" and mean that a query plan would have to be rebuilt each time the stored proc was run? I can understand that sometimes readability and maintanance considerations can outweigh the performance considerations, but don't you want to try to avoid dynamic sql if at all possible?
Thanks for your very prompt and helpful responses guys.
HainKurt, wouldn't your solution be considered "dynamic sql" and mean that a query plan would have to be rebuilt each time the stored proc was run? I can understand that sometimes readability and maintanance considerations can outweigh the performance considerations, but don't you want to try to avoid dynamic sql if at all possible?
Thanks for your very prompt and helpful responses guys.
What Hainkurt suggest is not dynamic SQL. dynamic SQL works by variabilizing strings which is different, and f course should be avoided for security reasons...
ASKER
Ahh, OK. I see the difference now that I look closer. Each SQL statement would be complete on its own, you are just changing which one you execute based on the input parameter. Apologies for the ignorance. Thanks for the help.
https://www.experts-exchange.com/A_3203.html
1 possibility might be this:
Open in new window