Avoid using CASE in GROUP BY and ORDER BY

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)

Avoid using CASE in GROUP BY and ORDER BY

Author

Topic

francism
Starting Member

22 Posts

Posted - 2010-03-02 : 17:03:42

A simple example:

SELECT
CASE
  WHEN price > 20.00 THEN 'Expensive'
  WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
  WHEN price < 10.00 THEN 'Inexpensive'
  ELSE 'Unknown'
END AS Budget,
title,
price

FROM
titles

GROUP BY
CASE
  WHEN price > 20.00 THEN 'Expensive'
  WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
  WHEN price < 10.00 THEN 'Inexpensive'
  ELSE 'Unknown'
END,
title,
price

ORDER BY
CASE
  WHEN price > 20.00 THEN 'Expensive'
  WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
  WHEN price < 10.00 THEN 'Inexpensive'
  ELSE 'Unknown'
END,
title,
price

I would like to avoid having to recode the CASE expression for the GROUP BY and ORDER BY. The above example is a simple query - I have a more complex one. Also, I require second CASE expression that depends on the conditions in the first one.

Thank you,

Francis.

Microsoft CRM 3 - SQL Server 2000

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 17:30:15

just an idea to use a derived table for that like this:


SELECT
dt.Budget,
title,
price

FROM
titles t

join 

(select 
  title_id, 
  CASE
    WHEN price > 20.00 THEN 'Expensive'
    WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
    WHEN price < 10.00 THEN 'Inexpensive'
    ELSE 'Unknown'
  END AS Budget
from titles)dt
on t.title_id = dt.title_id

GROUP BY
dt.Budget,
t.title,
t.price

ORDER BY
dt.Budget,
title,
price