Re: HELP! SQL Question

In article <312CCE9A.6496_at_a1.tch.harvard.edu>, "Beth J. Katcher" <katcher_at_a1.tch.harvard.edu> says:
>
>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
>
>In order to achieve the above output you only need the following:
>SELECT parameter, MAX(real_number)
>FROM your_table
>GROUP BY parameter
>
>With a large table this may not be very fast, and you may want to think about other ways to
>structure the data -- e.g., flagging the most recent row or moving history to another table.
>
>Beth

Another way (it may not be fast, but it is another way to try) is to use a subquery.

  select parameter, real_number
  from you_table YT
  where real_number = (select max(real_number) from your_table

                       where parameter = YT.parameter)

Again test it. You may also look at your indexing to see if an additional index(es) will speed it up. If you add index(es) make sure that the new index(es) do not affect your current SELECT statements. Received on Fri Feb 23 1996 - 00:00:00 CET