Dynamic Order By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Order By

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-12-16 : 09:45:51

Hi i have a Dynamic Order By working fine
but when i try to using the order by column that have as alias name
doent work,
see below 2 ways that works fine and the one Doesnt Work
Any ideias ?

Tks
C. Lages

*Works fine because Cli_code and Cli_zip are real table columns****

DECLARE @SORT CHAR(10)
SET @SORT = 'Zip'

SELECT CLI_CODE, CLI_NOME, CLI_ZIP, FROM CLIENTES
ORDER BY
CASE @sort
WHEN 'Code'
THEN Cli_code
WHEN 'Zip'
THEN cli_zip
END ASC

***Works fine because i am not using DYNAMIC Order by columns****

select mov_vendedor , sum(mov_qt) as Qty , sum(mov_mercadoria) as Value
FROM teste.dbo.moviment moviment
WHERE (mov_cliente between 100 and 200 )
group by mov_vendedor
order by value DESC

***Doesnt Works because in this case i use CASE to dynamic Order by****

DECLARE @SORT CHAR(10)
SET @SORT = 'Qty'

select mov_vendedor , sum(mov_qt) as Qty , sum(mov_mercadoria) as Value
FROM teste.dbo.moviment moviment
WHERE (mov_cliente between 100 and 200 )
group by mov_vendedor
order by
CASE @sort
WHEN 'Qty'
THEN Qty
WHEN 'Value'
THEN Value
END ASC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-16 : 09:51:11

You need to use Derived table. Try this

Select * from
(
select mov_vendedor , sum(mov_qt) as Qty , sum(mov_mercadoria) as Value
FROM teste.dbo.moviment moviment
WHERE (mov_cliente between 100 and 200 )
group by mov_vendedor
) T
order by
CASE @sort
WHEN 'Qty'
THEN Qty
WHEN 'Value'
THEN Value
END ASC

Madhivanan

Failing to plan is Planning to fail

CLages
Posting Yak Master

116 Posts

Posted - 2005-12-26 : 09:42:13

Tks, the way you suggested works fine.
But i Really dont understand how Derived table works.
I did not find anything in BOL about this

could you give me a brief Explanation how its works

tks

C. Lages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources