Order By Case revisited - 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

Transact-SQL (2000)

Order By Case revisited

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2001-12-04 : 16:00:56

I still can't figure out how to order by the alias for my count(*) column. This should be possible. Here is the complete proc. Please let me know if you have any ideas why this does not work. I am not a very good SQL developer so there are probably better ways of doing what I want to do here. Thanks.

-----------------------------------------------------------------

CREATE PROC dbo.PROC_SB_Symantec_Log_Entries_Infected_Machines_sel
@date AS datetime,
@virus AS varchar(50),
@col AS varchar(15),
@order AS varchar(4),
@numcomputers AS int OUTPUT
AS

IF @order = 'DESC'
SELECT COUNT(*) AS tot
,computer
,username
FROM TBL_Symantec_Log_Entries
WHERE event = 5 AND
EventTime > @date AND
Virus LIKE @virus
GROUP BY computer
,username
ORDER BY CASE
WHEN @col = 'computer' THEN computer
WHEN @col = 'username' THEN username
ELSE 'tot'
END DESC

ELSE
SELECT COUNT(*) AS tot
,computer
,username
FROM TBL_Symantec_Log_Entries
WHERE event = 5 AND
EventTime >= @date AND
Virus LIKE @virus
GROUP BY computer
,username
ORDER BY CASE
WHEN @col = 'computer' THEN computer
WHEN @col = 'username' THEN username
ELSE 'tot'
END ASC

SELECT @numcomputers = @@ROWCOUNT
GO

-----------------------------------------------------------
The ORDER BY for both the computer and username columns work fine both ascending and descending. But the order by alias "tot" does not work. Obviously I am doing something wrong related to the alias. I don't think the single quotes around tot are right. But that is the only way it will not throw an error on compile. If I say "ELSE tot" or "ELSE [tot]" I get "Invalid column name tot" when I compile.

Thanks as always,
Chris

Edited by - cbrinson on 12/04/2001 16:04:40

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-04 : 16:15:42

In the ELSE case, the result rows will be ordered arbitrarily, since the ORDER BY value (a literal string 'tot') is always the same. If you use a CASE here, the type of result values for the CASE must be the same and you cannot use result column aliases.


ORDER BY CASE 
WHEN @col = 'computer' THEN computer 
WHEN @col = 'username' THEN username
ELSE RIGHT('0000000000'+convert(varchar, count(*)), 10)
END

should be fine unless you have some truly stupendous counts!

Edited by - Arnold Fribble on 12/04/2001 16:17:24

cbrinson
Starting Member

36 Posts

Posted - 2001-12-04 : 16:39:39

quote:

the type of result values for the CASE must be the same and you cannot use result column aliases.

Ahh... now that would explain the problem huh? Thanks for the info. It finally works now!

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources