Re: aggregate function to get 'any' value

"Caspar von Seckendorff" <seckendorff_at_alphatec.de> wrote in message news:bcq8te$rm5$1_at_innferno.news.tiscali.de...
> Hi,
>
> I'm trying to improve performance of some sql-queries and I was wondering
> whether there is something like an ANY(field_name) aggregate function.
>
> Probably it's easiest to explain the problem by a simple example. Let's
say
> there is a table containing turnovers:
>
> company_id NUMBER(9),
> company_name VARCHAR2(20),
> turnover NUMBER(9,2),
> date DATE
>
> In this case it does not make to much sense to save the company name
within
> the same table, but this is just an example.
>
> Selecting company-info and total turnover by company could be achieved by
> the following:
>
> SELECT company_id, company_name, SUM(turnover) FROM test_table GROUP BY
> company_id, company_name
>
> However, if for some reason there are multiple company names for the same
> company_id, this would be a problem, because logically it should only be
> grouped by company_id.
>
> In this case I just want to get ANY company name. One way to achieve this
> would be:
>
> SELECT company_id, MAX(company_name), SUM(turnover) FROM test_table GROUP
BY
> company_id
>
> The result would be OK, but the DB still has to sort the group by
> company_name to find out the MAX. I'd like to avoid this step. Is there a
> way to tell the DB that it should simply return ANY company_name in the
> group? Or possibly even that it should return any company_name that is not
> null?
>
> Thanks,
>
> -Caspar
>
>

Your post only makes sense in very lousy designs. How could it be possible a company_id has multiple company names? You have a design without any primary key. No trick can make for that!

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address