Simple SQL question

[Spunkey]

Simple question:
In the query

SELECT Value, 'DummyValue' = IncrementingVariable FROM Table

How can you order by 'DummyValue'?

Same question, but with the way I need it...
In the query

DECLARE @Client_Code varchar(30)
DECLARE @Campaign_Code varchar(30)
DECLARE @Catalogue_ID int
DECLARE @Section_ID int
DECLARE @Points int
DECLARE @Country_Code int
DECLARE @SortOrder int
SET @Client_Code = 'XQX'
SET @Campaign_Code = 'DEM'
SET @Catalogue_ID = '151'
SET @Section_ID = '177'
SET @Points = '999999'	
SET @Country_Code = '1'
SET @SortOrder = '1'
	

	SELECT Products.ID, Products.Name, 'Points' = 
			CASE 
				WHEN EXISTS (
					SELECT cPoints_Value FROM tblAward_Value WITH (NOLOCK) 
					WHERE cAward_Code = Products.ID 
						AND cClient_Code = @Client_Code 
						AND cCampaign_Code = @Campaign_Code 
						AND cCountry_Code = @Country_Code
						AND cPoints_Value = @Points) 
					THEN (SELECT cPoints_Value FROM tblAward_Value WITH (NOLOCK) 
						WHERE cAward_Code = Products.ID 
							AND cClient_Code = @Client_Code 
							AND cCampaign_Code = @Campaign_Code 
							AND cCountry_Code = @Country_Code
							AND cPoints_Value = @Points)
				ELSE (SELECT cPoints_Value FROM tblAward_Value WITH (NOLOCK) 
					WHERE cAward_Code = Products.ID 
						AND cClient_Code = 'PMM' 
						AND cCampaign_Code = 'PMM' 
						AND cCountry_Code = @Country_Code
						AND cPoints_Value = @Points)
			END
	FROM Products 
		INNER JOIN Product_Display ON Products.ID = Product_Display.Product_ID 
	WHERE Product_Display.Client_Code LIKE @Client_Code
		AND Product_Display.Campaign_Code LIKE @Campaign_Code
		AND Product_Display.Catalogue_ID = @Catalogue_ID
		AND Product_Display.Section_ID = @Section_ID
	ORDER BY CASE @SortOrder -- varchar fields
			WHEN 1 THEN Products.Name	
			ELSE Products.Name
		END

How can you order by 'Points'?

TIA

[Stoo]

Order by 3

isn't it?

3rd item selected?

[Spunkey]

tried that one

dynamic order by's are really naff. They really restrict the useablity of the order by. I have been looking for a function to pass the column ID into but no joy.

I think I'm just going to have to do
If Sortorder = 2
SELECT.... ORDER BY Points
Else
SELECT.... ORDER BY Products.Name

Crappy I know, but it works. And it avoids having to use GetRows in the ASP code this is built on, which is a result as far as I'm concerned

Cheers Stoo

[TiG]

As you say cross tab's reports cause you to have restrictions imposed on the order by.

You may consider breaking the query down into sub parts and doing multiple queries to get the data set and orders you want.

Its blooming difficult to help you as this data set is complicated, CrossTab's are difficult to imagine too without some underlying data to back up the query.

TiG

[happytechie]

SELECT Value, 'Points' = Big sub query natyness AS foo FROM Table ORDER BY foo

adding a column alias to your 'Points' should do it I think

HT

[Glaivier]

The main approach that springs to mind is to build a view around selecting with the derived value, and then build simple order by clauses in the final select statement. This also can help optimize the code a little by storing the definition files on the server for this view, rather than recreating them when called.