Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news.moat.net!news-out.newsfeeds.com!propagator2-maxim!news-in-maxim.spamkiller.net!news.he.net!chekhov.conxion.net!news.oracle.com!not-for-mail From: "Mikito Harakiri" Newsgroups: comp.databases.theory References: <3f1f1b9a@news.comindico.com.au> <29e28c51.0307241531.7aee525c@posting.google.com> <3f20b2e5@news.comindico.com.au> <29e28c51.0307250334.3ddf7f4d@posting.google.com> Subject: Re: Urgent help Lines: 45 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Fri, 25 Jul 2003 10:05:17 -0700 NNTP-Posting-Host: 130.35.178.25 X-Trace: news.oracle.com 1059153201 130.35.178.25 (Fri, 25 Jul 2003 10:13:21 PDT) NNTP-Posting-Date: Fri, 25 Jul 2003 10:13:21 PDT Xref: newssvr20.news.prodigy.com comp.databases.theory:20703 "Cimarron Taylor" wrote in message news: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 Did you mean "group by"? > 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 This was not a part of the original problem. Anybody able to write a query in "clean" scenario, could amend it to handle nulls.