pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.






view thread (3+ messages)

Message-ID: <20011210131528.C70079-100000@megazone23.bigpanda.com>
Permalink:  ../20011210131528.C70079-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20011210131528.C70079-100000@megazone23.bigpanda.com

 · 

reply

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