ORDER BY with CASE and nested query

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 2005 Forums

Transact-SQL (2005)

ORDER BY with CASE and nested query

Author

Topic

ias0nas
Starting Member

36 Posts

Posted - 2010-08-20 : 04:57:01

Hello,

I have a query that looks like this:

SELECT column1,
(SELECT SUM(column2) FROM table2) AS total_count
FROM table1
ORDER BY
CASE WHEN @OrderByOrder = 'ASC' THEN
CASE WHEN @OrderByColumn = 'column1' THEN CAST(table1.column1 AS NVARCHAR(10))
ELSE table1.column2
END
END ASC,
CASE WHEN @OrderByOrder = 'DESC' THEN
CASE WHEN @OrderByColumn = 'column1' THEN CAST(table1.column1 AS NVARCHAR(10))
ELSE table1.column2
END
END DESC

I would like to be able to sort on total_count column. If I use the column name total_count SQL is complaining there is not such column. If I use the whole SELECT statement, the query doesn't return anything.

Is there a way of doing this?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-20 : 05:21:21

you can use Dynamic SQL The Curse and Blessings of Dynamic SQL

or


; with 
data
as
(
	SELECT 	column1, column2,
		(SELECT SUM(column2) FROM table2) AS total_count
	FROM 	table1
),
data2
as
(
	select	column1, column2, total_count,
		row_no = case 	@OrderByColumn
				when 'column1' 		then row_number() over (order by column1)
				when 'column2' 		then row_number() over (order by column2)
				when 'total_count' 	then row_number() over (order by total_count)
				end
	from	data
)
select	column1, column2, total_count
from	data2
order by case when @OrderBy = 'ASC' then row_no end,
	 case when @OrderBy = 'DESC' then row_no end desc

KH
[spoiler]Time is always against us[/spoiler]

ias0nas
Starting Member

36 Posts

Posted - 2010-08-20 : 05:23:44

Thanks khtan, got it working like this: (I was misplacing the cast)

SELECT column1,
(SELECT SUM(column2) FROM table2) AS total_count
FROM table1
ORDER BY
CASE WHEN @OrderByOrder = 'ASC' THEN
CASE WHEN @OrderByColumn = 'column1' THEN CAST(table1.column1 AS NVARCHAR(10))
ELSE table1.column2
END
END ASC,
CASE WHEN @OrderByOrder = 'DESC' THEN
CASE WHEN @OrderByColumn = 'column1' THEN CAST(table1.column1 AS NVARCHAR(10))
CASE WHEN @OrderByColumn = 'total_count' THEN (SELECT CAST(SUM(column2) AS nvarchar(10)) FROM table2)
ELSE table1.column2
END
END DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-20 : 05:30:55

quote:
CASE WHEN @OrderByColumn = 'total_count' THEN (SELECT CAST(SUM(column2) AS nvarchar(10)) FROM table2)

you are casting the total count to string. When you order by the string, 1000 will come before 2 and 3

KH
[spoiler]Time is always against us[/spoiler]

ias0nas
Starting Member

36 Posts

Posted - 2010-08-20 : 05:41:29

Yes, your solution seems a lot nicer and I am trying that now, but I got this:

The multi-part identifier "job.job" could not be bound.
job is an alias, is there a problem with that?

Thanks

ias0nas
Starting Member

36 Posts

Posted - 2010-08-20 : 06:20:34

The problem was the prefix, I was putting the table name before column1 in the order by

Thanks khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources