Solved

SELECT DISTINCT w/Join, ORDER BY in CASE

Posted on 2010-09-15
7
703 Views
Last Modified: 2012-08-13
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!


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

Open in new window

0
Comment
Question by:Jahelka
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33682770
you migth want to read this article:
http://www.experts-exchange.com/A_3203.html

1 possibility might be this:

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 SiteIDSiteName, SiteID, SiteName

          FROM ( 

           SELECT convert(varchar,SD.SiteID) + ' - ' + SiteName AS SiteIDSiteName

            , SD.SiteID, SiteName 

            , CASE 

              WHEN @SortOrder = 'SiteName' THEN (RANK() OVER (ORDER BY SiteName))

              ELSE (RANK() OVER (ORDER BY SD.SiteID))

               END rn

            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)

        ) sq

        GROUP BY SiteIDSiteName, SiteID, SiteName

        ORDER BY MAX(rn)

                

END

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33682778
do this

if @SortOrder="..."
begin
  select ...
end

if @SortOrder="..."
begin
  select ...
end

use different simple statements instead of trying to order by complicated statements...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 33682830
Do what HainKurt suggested.  Better both for readability and performance.  (pls no poinks)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Accepted Solution

by:
Valliappan AN earned 500 total points
ID: 33682853
Try removing the distinct and use GROUP BY as attached. Hope that helps
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 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)
        GROUP BY convert(varchar,SD.ID) + ' - ' + sd.Name , SD.ID, sd.Name
	ORDER BY
		CASE
			WHEN @SortOrder = 'SiteName' THEN (RANK() OVER (ORDER BY SiteName))
			ELSE (RANK() OVER (ORDER BY SD.SiteID))
		END
END

Open in new window

0
 
LVL 1

Author Closing Comment

by:Jahelka
ID: 33683037
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 33683259
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...
0
 
LVL 1

Author Comment

by:Jahelka
ID: 33683464
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 77
SSIS import multiple CSVs into associated tables 3 32
Stored procedure 23 25
Query 14 22
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now