pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect
10+ messages / 8 participants
[nested] [flat]
* select
@ 2000-09-23 17:37 Jeff MacDonald <jeff@pgsql.com>
0 siblings, 3 replies; 10+ messages in thread
From: Jeff MacDonald @ 2000-09-23 17:37 UTC (permalink / raw)
To: pgsql-sql
how would i select all rows where a boolean value is neither
t nor f.. ?
ie if someone inserted without setting the boolean tag.
Jeff MacDonald,
-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2000-09-23 17:49 Jeff MacDonald <jeff@pgsql.com>
parent: Indraneel Majumdar <indraneel@www.cdfd.org.in>
0 siblings, 0 replies; 10+ messages in thread
From: Jeff MacDonald @ 2000-09-23 17:49 UTC (permalink / raw)
To: Indraneel Majumdar <indraneel@www.cdfd.org.in>; +Cc: Jeff MacDonald <jeff@pgsql.com>; pgsql-sql
bignose=# create table test (
bignose(# val1 int4,
bignose(# val2 boolean);
CREATE
bignose=#
bignose=# insert into test (val1) values (56);
INSERT 322762 1
bignose=#
bignose=# select * from test;
val1 | val2
------+------
56 |
(1 row)
ta da :)
On Sat, 23 Sep 2000, Indraneel Majumdar wrote:
> Hi,
>
> AFAIK, you can't enter a null value in a bool field, it has to be 1 or 0.
>
> \Indraneel
>
> On Sat, 23 Sep 2000, Jeff MacDonald wrote:
>
> > how would i select all rows where a boolean value is neither
> > t nor f.. ?
> >
> > ie if someone inserted without setting the boolean tag.
> >
> > Jeff MacDonald,
> >
>
> /************************************************************************.
> # Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
> # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
> # Centre for DNA Fingerprinting and Diagnostics, #
> # Hyderabad, India - 500076 #
> `************************************************************************/
>
Jeff MacDonald,
-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2000-09-23 18:49 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Jeff MacDonald <jeff@pgsql.com>
2 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2000-09-23 18:49 UTC (permalink / raw)
To: Jeff MacDonald <jeff@pgsql.com>; +Cc: pgsql-sql
I'd assume this would work:
select * from table where booleanfield is null;
Stephan Szabo
sszabo@bigpanda.com
On Sat, 23 Sep 2000, Jeff MacDonald wrote:
> how would i select all rows where a boolean value is neither
> t nor f.. ?
>
> ie if someone inserted without setting the boolean tag.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2000-09-23 18:52 Jeff MacDonald <jeff@pgsql.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jeff MacDonald @ 2000-09-23 18:52 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Jeff MacDonald <jeff@pgsql.com>; pgsql-sql
bingo,
thaks
On Sat, 23 Sep 2000, Stephan Szabo wrote:
>
> I'd assume this would work:
> select * from table where booleanfield is null;
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Sat, 23 Sep 2000, Jeff MacDonald wrote:
>
> > how would i select all rows where a boolean value is neither
> > t nor f.. ?
> >
> > ie if someone inserted without setting the boolean tag.
>
>
Jeff MacDonald,
-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2000-09-24 06:21 Indraneel Majumdar <indraneel@www.cdfd.org.in>
parent: Jeff MacDonald <jeff@pgsql.com>
2 siblings, 1 reply; 10+ messages in thread
From: Indraneel Majumdar @ 2000-09-24 06:21 UTC (permalink / raw)
To: Jeff MacDonald <jeff@pgsql.com>; +Cc: pgsql-sql
Hi,
AFAIK, you can't enter a null value in a bool field, it has to be 1 or 0.
\Indraneel
On Sat, 23 Sep 2000, Jeff MacDonald wrote:
> how would i select all rows where a boolean value is neither
> t nor f.. ?
>
> ie if someone inserted without setting the boolean tag.
>
> Jeff MacDonald,
>
/************************************************************************.
# Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`************************************************************************/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2000-09-25 18:45 Jie Liang <jliang@ipinc.com>
parent: Jeff MacDonald <jeff@pgsql.com>
2 siblings, 0 replies; 10+ messages in thread
From: Jie Liang @ 2000-09-25 18:45 UTC (permalink / raw)
To: Jeff MacDonald <jeff@pgsql.com>; +Cc: pgsql-sql
Hi, there,
only possible is null, so select blah from tableblah where field is
null;
Jeff MacDonald wrote:
> how would i select all rows where a boolean value is neither
> t nor f.. ?
>
> ie if someone inserted without setting the boolean tag.
>
> Jeff MacDonald,
>
> -----------------------------------------------------
> PostgreSQL Inc | Hub.Org Networking Services
> jeff@pgsql.com | jeff@hub.org
> www.pgsql.com | www.hub.org
> 1-902-542-0713 | 1-902-542-3657
> -----------------------------------------------------
> Fascimile : 1 902 542 5386
> IRC Nick : bignose
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* select
@ 2003-10-09 15:09 roberto <colorob@tin.it>
0 siblings, 1 reply; 10+ messages in thread
From: roberto @ 2003-10-09 15:09 UTC (permalink / raw)
To: pgsql-sql
Dear friends,
I have this table
table work{
day date,
hour integer,
}
select * from work;
date | text
-----------
1-1-2003 1
1-1-2003 1
2-1-2003 5
3-1-2003 10
5-1-2003 15
how can i obtain this?
date | text
-----------
1-1-2003 2
2-1-2003 5
3-1-2003 10
4-1-2003 null
5-1-2003 15
6-1-2003 null
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2003-10-09 16:58 Janko Richter <jankorichter@yahoo.de>
parent: roberto <colorob@tin.it>
0 siblings, 0 replies; 10+ messages in thread
From: Janko Richter @ 2003-10-09 16:58 UTC (permalink / raw)
To: colorob@tin.it; pgsql-sql
roberto wrote:
> Dear friends,
> I have this table
>
> table work{
> day date,
> hour integer,
> }
>
> select * from work;
>
> date | text
> -----------
> 1-1-2003 1
> 1-1-2003 1
> 2-1-2003 5
> 3-1-2003 10
> 5-1-2003 15
>
> how can i obtain this?
>
> date | text
> -----------
> 1-1-2003 2
> 2-1-2003 5
> 3-1-2003 10
> 4-1-2003 null
> 5-1-2003 15
> 6-1-2003 null
>
>
First , you need a sequence of days. Just create
a function like this:
CREATE OR REPLACE FUNCTION dateseq ( DATE , DATE )
RETURNS SETOF DATE
LANGUAGE 'plpgsql'
AS '
DECLARE
bdate ALIAS FOR $1 ;
edate ALIAS FOR $2 ;
cdate DATE ;
BEGIN
cdate := bdate;
WHILE cdate <= edate LOOP
RETURN NEXT cdate ;
cdate := CAST ( cdate + interval ''1 day'' AS date );
END LOOP;
RETURN;
END;
';
The function is like a table/view , where the fist function argument
is the start date , the second argument is the end date.
Now try :
SELECT ds.day, sum(w.hour)
FROM dateseq( '2003-1-1', '2003-1-6' ) AS ds (day)
LEFT JOIN work w ON ds.day=w.day
GROUP BY ds.day;
Regards, Janko
--
Janko Richter
^ permalink raw reply [nested|flat] 10+ messages in thread
* select
@ 2004-08-07 11:54 Knut P Lehre <k.p.lehre@world-online.no>
0 siblings, 1 reply; 10+ messages in thread
From: Knut P Lehre @ 2004-08-07 11:54 UTC (permalink / raw)
To: pgsql-sql
I have a table with fields:
id A B C D E F
where id is an int4 primary key.
In this table there is information like:
1 a1 b1 xxx xxx
2 a1 b1 xxx xxx xxx xxx
3 a2 b2 xxx xxx xxx xxx
4 a2 b2 xxx xxx xxx xxx
5 a3 b3 xxx xxx xxx
6 a3 b3 xxx xxx xxx xxx
7 a3 b3 xxx xxx xxx xxx
8 a4 b4 xxx xxx xxx xxx
9 a1 b1 xxx
10 a3 b3 xxx
11 a1 b3 xxx
where xxx represents any information.
My problem is: I only want to select one of the records which have the same
combination of information in fields A and B, and that should be the record
with the lowest id. Thus, the resultset should be:
1 a1 b1 xxx xxx
3 a2 b2 xxx xxx xxx xxx
5 a3 b3 xxx xxx xxx
8 a4 b4 xxx xxx xxx xxx
11 a1 b3 xxx
Importantly, I might not want the selected records listed in order of increasing
id. They might be sorted according to the data in e.g. the C field.
Suggestions/comments greatly appreciated.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: select
@ 2004-08-09 14:53 Christoph Haller <ch@rodos.fzk.de>
parent: Knut P Lehre <k.p.lehre@world-online.no>
0 siblings, 0 replies; 10+ messages in thread
From: Christoph Haller @ 2004-08-09 14:53 UTC (permalink / raw)
To: k.p.lehre@basalmed.uio.no; +Cc: pgsql-sql
Knut P Lehre wrote:
> I have a table with fields:
> id A B C D E F
> where id is an int4 primary key.
> In this table there is information like:
> 1 a1 b1 xxx xxx
> 2 a1 b1 xxx xxx xxx xxx
> 3 a2 b2 xxx xxx xxx xxx
> 4 a2 b2 xxx xxx xxx xxx
> 5 a3 b3 xxx xxx xxx
> 6 a3 b3 xxx xxx xxx xxx
> 7 a3 b3 xxx xxx xxx xxx
> 8 a4 b4 xxx xxx xxx xxx
> 9 a1 b1 xxx
> 10 a3 b3 xxx
> 11 a1 b3 xxx
> where xxx represents any information.
> My problem is: I only want to select one of the records which have the same
> combination of information in fields A and B, and that should be the record
> with the lowest id. Thus, the resultset should be:
> 1 a1 b1 xxx xxx
> 3 a2 b2 xxx xxx xxx xxx
> 5 a3 b3 xxx xxx xxx
> 8 a4 b4 xxx xxx xxx xxx
> 11 a1 b3 xxx
> Importantly, I might not want the selected records listed in order of increasing
> id. They might be sorted according to the data in e.g. the C field.
>
> Suggestions/comments greatly appreciated.
>
Does this give you the result you are looking for?
SELECT * FROM (
SELECT DISTINCT ON (A,B) * FROM table ORDER BY id
) AS foo ORDER BY C ;
Regards, Christoph
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-08-09 14:53 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-23 17:37 select Jeff MacDonald <jeff@pgsql.com>
2000-09-23 18:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-09-23 18:52 ` Jeff MacDonald <jeff@pgsql.com>
2000-09-24 06:21 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-09-23 17:49 ` Jeff MacDonald <jeff@pgsql.com>
2000-09-25 18:45 ` Jie Liang <jliang@ipinc.com>
2003-10-09 15:09 select roberto <colorob@tin.it>
2003-10-09 16:58 ` Janko Richter <jankorichter@yahoo.de>
2004-08-07 11:54 select Knut P Lehre <k.p.lehre@world-online.no>
2004-08-09 14:53 ` Christoph Haller <ch@rodos.fzk.de>
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