Re: HELP! SQL Question

John N. Zeigler wrote:
>
> We have a very large database with historical information. When we
> update information, we add a new row and keep the old info. We do this
> by using a real number column that is similar to a date field. That is,
> the larger the number, the more recent the data. So, to extract the most
> recent information, I want to select the rows with the highest real
> numbers. For example, if I have 3 rows with information on parameter A
> I want to extract the one row with the largest real number. I may have
> 5 rows for parameter B, 12 rows for parameter C, etc. For each of these
> parameters, I want only the row with the highest real number. For my
> output, I want one row returned for each parameter and this row must
> contain the largest value in the real number column for that parameter.
> Example of output:
>
> Parameter Real_number
>
> A 5.3
> B 3.1
> C 6.2
> D 1.4
> E 9.2

There's no quick way to get to the data especially if the table is very very large. One thing you could try is:

SELECT *
FROM BIG_TABLE B1
WHERE REAL_NUMBER = (SELECT MAX(REAL_NUMBER)

                      FROM   BIG_TABLE B2
                      WHERE  B2.KEY = B1.KEY)

This sort of correlated subquery can take a long time.

Another approach is to create a view to get to the data:

CREATE VIEW V_BIGGEST_NUMBERS
AS
SELECT B.KEY, MAX(B.REAL_NUMBER)
FROM BIG_TABLE
GROUP BY B.KEY Using this view you can join back to the BIG_TABLE:

SELECT ...
FROM V_BIGGEST_NUMBERS V, BIG_TABLE B
WHERE B.KEY = V.KEY
AND B.REAL_NUMBER = V.REAL_NUMBER This query is still going to take a considerable amount of time to execute.

You might look into the use of triggers to maintain a separate "current" version of your data.

-Erik. Received on Mon Feb 26 1996 - 00:00:00 CET