Retrieve ONLY first/max

David Portas wrote:

Quote:

Originally Posted by

Alternatively you can do the following using standard ANSI SQL, which should
work on many different platforms:
>
SELECT type
FROM ord, product
WHERE ord.id = product.id
GROUP BY type
HAVING SUM(units) >= ALL
(SELECT DISTINCT SUM(units)
FROM ord, product
WHERE ord.id = product.id
GROUP BY type);
>
(untested)

This could return multiple values if there's a tie for most
common type.On 18 Sep, 08:30, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

>
This could return multiple values if there's a tie for most
common type.

Correct. So could the version using TOP WITH TIES. Mark said: "I want
to know the type that has the max units in any group". If there is
more than one such type then the specification is incomplete because
Mark doesn't say which one should come "first". Rather than pick a
random row or make the assumption that there is only one row I decided
it was safer to return everything - that way Mark can decide for
himself whether he needs to refine his spec.

--
David Portas|||On Sep 18, 5:27 am, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

On 18 Sep, 08:30, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

This could return multiple values if there's a tie for most
common type.

>
Correct. So could the version using TOP WITH TIES. Mark said: "I want
to know the type that has the max units in any group". If there is
more than one such type then the specification is incomplete because
Mark doesn't say which one should come "first". Rather than pick a
random row or make the assumption that there is only one row I decided
it was safer to return everything - that way Mark can decide for
himself whether he needs to refine his spec.
>
--
David Portas

Thanks guys. You'd think they'd have a "standard" (and simple) method
for doing this eh?