Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Can anybody help me with SQL?

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Richard Lockwood <rickardo2010(at)hotmail(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: [SQL] Can anybody help me with SQL?
Date: 2001-12-10 21:33:54
Message-ID: 20011210131528.C70079-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql

> 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.




In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2001-12-10 22:40:04
Subject: Re: Storing number '001' ?
Previous:From: Josh BerkusDate: 2001-12-10 20:48:42
Subject: Re: Backup automation

pgsql-sql by date

Next:From: sharmadDate: 2001-12-11 03:48:54
Subject: Indices
Previous:From: Stephan SzaboDate: 2001-12-10 21:09:39
Subject: Re: serial sequences not automatically dropped

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group