db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Selecting max/min
Date Mon, 05 Sep 2011 11:02:22 GMT
I have a table of results for several tests of several items where each
of the results is measured at different times, and I want to be able to
get the complete rows corresponding to the largest, smallest and most
recent result for each test on each item. I can for example get the
largest result for each test on each item like so:

   SELECT item,test,MAX(result) AS maxr FROM results GROUP BY item,test;

but if I then want the whole row for each test on a single item, I end
up doing something like this:

   CREATE VIEW max_view AS
       SELECT item,test,MAX(result) AS maxr
       FROM results GROUP BY item,test;
   SELECT * FROM results,max_view
       WHERE results.item=?
       AND   results.item=max_view.item AND result=maxr;

So with three views (max, min and latest) I can select from the results
table and the three views to pull out the rows I need. This looks as if
it will be horribly inefficient (particularly for big tables), what with
selecting stuff from the same table four times and then throwing most of
it away.

Can anyone suggest a more elegant solution?

TIA,

------------------------------------------------------------------------
  John English | My old University of Brighton home page is still here:
               | http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------

Mime
View raw message