Basic Aggregate/HAVING question

I want to find the order that has the largest quanity in pubs.dbo.sales
If I try to use an aggregate in the WHERE clause I get an error
--
SELECT ord_num
FROM pubs.dbo.sales
WHERE qty=max(qty)
/*
An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause
or a select list, and the column being aggregated is an outer reference.
*/
But I am almost certain that I used to be able to do this in Sybase T-SQL
and it worked:
SELECT ord_num
FROM pubs.dbo.sales
HAVING qty=max(qty)
Now in MS T-SQL I get these errors:
/*
Column 'sales.ord_num' is invalid in the select list because it is not
contained in an aggregate function
and there is no GROUP BY clause.
Column 'sales.qty' is invalid in the HAVING clause because it is not
contained in an aggregate function
and there is no GROUP BY clause.
*/
It looks like I can get what I want by either one of two ways
1. A subquery:
SELECT ord_num, qty
FROM pubs.dbo.sales
WHERE qty=(SELECT max(qty) FROM sales)
/*
ord_num qty
-- --
QA7442.3 75
*/
2. A GROUP BY with TOP
SELECT TOP 1 ord_num, max(qty) AS 'qty'
FROM pubs.dbo.sales
GROUP BY ord_num
ORDER BY max(qty) DESC
/*
ord_num qty
-- --
QA7442.3 75
*/
I have 2 questions:
1. Are there other (better) ways to write this?
2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?> But I am almost certain that I used to be able to do this in Sybase T-SQL
> and it worked:
> SELECT ord_num
> FROM pubs.dbo.sales
> HAVING qty=max(qty)
Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause is
applied after the aggregation so it doesn't make sense to reference a base
table column in this way unless it exists in the aggregate result.
> 1. Are there other (better) ways to write this?
I doubt it's any "better" but just for variety here's an alternative:
SELECT ord_num, qty
FROM Sales
WHERE qty >= ALL
(SELECT qty
FROM Sales)
I prefer the WHERE qty= subquery method.
> 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?
Yes. HAVING without GROUP BY implies an aggregation across the whole set in
the same way that an aggregate function does without GROUP BY. The result
will be, at most, one row. You can use HAVING to return a value only if some
condition is met:
SELECT MIN(ord_date)
FROM Sales
HAVING MIN(ord_date)<='20000101'
And it can be especially useful in conjunction with EXISTS:
...
EXISTS -- Is ord_date unique?
(SELECT 1
FROM Sales
HAVING COUNT(DISTINCT ord_date)=COUNT(*))
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David for the detailed answers.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:H4idnZ3fHI4t8lOi4p2dnA@.giganews.com...
> > But I am almost certain that I used to be able to do this in Sybase
T-SQL
> > and it worked:
> >
> > SELECT ord_num
> > FROM pubs.dbo.sales
> > HAVING qty=max(qty)
> Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
> syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause
is
> applied after the aggregation so it doesn't make sense to reference a base
> table column in this way unless it exists in the aggregate result.
>
> > 1. Are there other (better) ways to write this?
> I doubt it's any "better" but just for variety here's an alternative:
> SELECT ord_num, qty
> FROM Sales
> WHERE qty >= ALL
> (SELECT qty
> FROM Sales)
> I prefer the WHERE qty= subquery method.
>
> > 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so,
when?
> Yes. HAVING without GROUP BY implies an aggregation across the whole set
in
> the same way that an aggregate function does without GROUP BY. The result
> will be, at most, one row. You can use HAVING to return a value only if
some
> condition is met:
> SELECT MIN(ord_date)
> FROM Sales
> HAVING MIN(ord_date)<='20000101'
> And it can be especially useful in conjunction with EXISTS:
> ...
> EXISTS -- Is ord_date unique?
> (SELECT 1
> FROM Sales
> HAVING COUNT(DISTINCT ord_date)=COUNT(*))
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>