Re: Urgent help

From: Cimarron Taylor <cimarron+google_at_taylors.org>
Date: 25 Jul 2003 04:34:20 -0700
Message-ID: <29e28c51.0307250334.3ddf7f4d@posting.google.com>

> Cim,
> Sorry, could you tell me why do I need to use the max please ?

It appears that your problem is that you need to aggregate all the records with a given bucket id into a single record and the easist way to do this is to use an aggregate expression in your select statement:

  select BucketID,

       , max(IF(WaterLevel='Empty', Time, '')) as Empty
       , max(IF(WaterLevel='Half', Time, '')) as Half
       , max(IF(WaterLevel='Full', Time, '')) as Full

    from Bucket
   order by BucketID

The max() expression in each case is simply returning the largest Time for each specified WaterLevel. This may produce the result you want or not depending on the actual semantics of the information in your table and how you want the information to appear.

Before doing much more, I would recommend you write a few sentences describing the actual meaning of each record in your table, what logical constraints you may assume, and what specific output you want in various cases. The create table statement you used for the table is a good place to start.

For example, could the table contain the following data and how would you want it presented?

   BucketID Time WaterLevel

---

   1           9:00    Empty
   1          10:00    Half
   1                   Full
   1          11:00    Full
   1          12:00    Empty
   1          12:00    Full
   1          12:00    
   1          13:00    Half
   1          14:00    Half

Cim Received on Fri Jul 25 2003 - 06:34:20 CDT