Grouping expression question (with demo application)

[alexis]

Hi

I attach a demo application (see attached qvw and xls).

In this demo application I have 3 tables:

a) Clients (linked to Orders via ClientID)

b) Orders (linked to Prices via OrderID)

c) Prices

For every order there are multiple records in Prices. Please also note the field "Type" that is present in both "Orders" and "Prices" (but are not linked).

As it explains in the yellow box, I want to extend the Orders table with 2 additional columns "MaxPrice" and "MinPrice" that will contain the maximum and minimum prices for that OrderID/Type combination.

This will be achieved (I suspect) by traversing the Prices table for every Order and the result should be as in the BLUE table (see screen shot).

Please help as I am stuck - simply doing Max(Price) and Min(Price) does not cover for the "Type" part as explained above.

Thanx in advance

Alexis

[alexis]

This application is using BILLIONS of price rows!!!

The demo application that I created is not the appliction that I created - it is merely a way of demonstrating the requirement.

Thank you very much for your help..

I selected your previous answer as the Correct answer in the end!!

Alexis

[#3]

> The demo application that I created is not the appliction that I created - it is merely a way of demonstrating the requirement.

Of course !

Just to be curious, could you tell me how many time it takes:

- to load the "prices" table the first time

- to load the "orders" table

- to make the LEFT JOIN / GROUP BY, using the resident table.

I bet the join is very faster than the other loads.

[alexis]

I will let you know as soon as I have made some more progress for sure.

Thanx again

Alexis