aggregate function to get 'any' value

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 Received on Wed Jun 18 2003 - 12:50:36 CDT