Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBALXrm29333; Mon, 10 Dec 2001 16:33:57 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D227CD5E9; Mon, 10 Dec 2001 13:33:54 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id C5D4A5BEE; Mon, 10 Dec 2001 13:33:54 -0800 (PST) Date: Mon, 10 Dec 2001 13:33:54 -0800 (PST) From: Stephan Szabo To: Richard Lockwood Cc: , Subject: Re: [SQL] Can anybody help me with SQL? In-Reply-To: Message-ID: <20011210131528.C70079-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200112/57 X-Sequence-Number: 2601 > 1.Which car was rented for the longest single period? (regno) > > SELECT regno, MAX(datein-dateout) "Longest Single Period" FROM rental group > by regno; > > Although this works - it shows ALL the regno's longest periods - how would i > just display the regno? There's probably a better way, but if I'm getting the question you want answered right I think these will do it. select regno, m from rental, (select max(datein-dateout) as m from rental) foo where m=datein-dateout; or select regno, datein-dateout from rental where datein-dateout=(select max(datein-dateout) from rental); > 2. Which car was rented for the longest total period? (regno) Maybe something like this? Not entirely sure select regno, t from (select regno, sum(datein-dateout) as t from rental group by regno) r where t=(select max(total) from (select sum(datein-dateout) as total from rental group by regno) foo); > 3. Which customers (if any) were born on Monday? (surname) > > For this question i thought that this would work: > > SELECT * FROM cust WHERE TO_CHAR(cd_o_b,'DAY') = 'MONDAY' > > But it never works - it says 'no rows selected' > although there are 2 cusotmers born on Monday > However if you put wednesday instead of monday or any other day it seems to > work correctly > Anyone know why this is? To_char fills out to a given length with spaces, I'd guess that the value is actually something like 'MONDAY ' I think you probably want the format FMDAY which should not have the spaces.