From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Richard Lockwood <rickardo2010@hotmail.com>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Can anybody help me with SQL?
Date: Mon, 10 Dec 2001 13:33:54 -0800 (PST)
Message-ID: <20011210131528.C70079-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <F113GXix4A2DreBBsQw000012d9@hotmail.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, rickardo2010@hotmail.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] Can anybody help me with SQL?
In-Reply-To: <20011210131528.C70079-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox