SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, July 03, 2006

group by qst

SQL Apprentice Question
In the following query I want to return as a last column the aggregate max
value of the OrderInList field.

input >>
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by *
order by OrderInList

output >>
Column 'tbl_activities.Activity_ID' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause.

Can I achieve what I want by including the extra MaxOrderInList column
instead of returning an output param?

Celko Answers
>> Last column is to return the same value for all records [sic], like this ..<<

Try a scalar subquery expression

SELECT a, b, c, ...
(SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
FROM Foobar;

No comments: