pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery assistance
7+ messages / 5 participants
[nested] [flat]
* query assistance
@ 2003-11-04 15:16 Jodi Kanter <jkanter@virginia.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Jodi Kanter @ 2003-11-04 15:16 UTC (permalink / raw)
To: pgsql-sql
Is there a straight forward way to pull out duplicates in a particular
field given a value in another field?
For example, I have a table that lists users and study names associated
with those users. Each user can have one or more study names. My goal is
to determine if any of these people have duplicate study names. There
are duplicated study names across the system and that is ok. I just want
to see if any users have duplicate study names among their studies.
My table looks like this:
Table "public.study"
Column | Type |
Modifiers
--------------+-----------------------------+------------------------------------------
sty_pk | integer | not null default
nextval('pk_seq'::text)
study_name | character varying(128) | not null
start_date | timestamp without time zone |
sty_comments | text |
created_by | integer |
Indexes: study_pkey primary key btree (sty_pk)
I am concerened with study_name. The created_by field tells me who owns it.
Thanks
Jodi
--
/_______________________________
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>/
/ /
/ /
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query assistance
@ 2003-11-04 15:43 Richard Huxton <dev@archonet.com>
parent: Jodi Kanter <jkanter@virginia.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2003-11-04 15:43 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; pgsql-sql
On Tuesday 04 November 2003 15:16, Jodi Kanter wrote:
> Is there a straight forward way to pull out duplicates in a particular
> field given a value in another field?
> For example, I have a table that lists users and study names associated
> with those users. Each user can have one or more study names. My goal is
> to determine if any of these people have duplicate study names. There
> are duplicated study names across the system and that is ok. I just want
> to see if any users have duplicate study names among their studies.
Something like:
SELECT
created_by, study_name, count(sty_pk)
FROM
study
GROUP BY
created_by, study_name
HAVING
count(sty_pk) > 1;
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query assistance
@ 2003-11-05 04:09 Michael Glaesemann <grzm@myrealbox.com>
parent: Jodi Kanter <jkanter@virginia.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Glaesemann @ 2003-11-05 04:09 UTC (permalink / raw)
To: pgsql-sql
Hi Jodi,
On Wednesday, November 5, 2003, at 12:16 AM, Jodi Kanter wrote:
> Is there a straight forward way to pull out duplicates in a particular
> field given a value in another field?
> For example, I have a table that lists users and study names
> associated with those users. Each user can have one or more study
> names.
If I understand you correctly, this is the table you're interested in.
Your public.study table doesn't include any users as far as I can tell
(though please correct me if I'm misunderstanding you).
> Table "public.study"
> Column | Type |
> Modifiers
> --------------+-----------------------------
> +------------------------------------------
> sty_pk | integer | not null default
> nextval('pk_seq'::text)
> study_name | character varying(128) | not null
> start_date | timestamp without time zone |
> sty_comments | text |
> created_by | integer |
> Indexes: study_pkey primary key btree (sty_pk)
I think something like this is what you're looking for:
SELECT user, study_name, COUNT(*)
FROM <table linking user and study_name>
GROUP BY user, study_name
HAVING COUNT(*) > 1;
where the FROM clause lists the table linking users and study_names.
Does this help?
Michael
grzm myrealbox com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Query Assistance
@ 2007-12-12 16:58 Gary Chambers <gwchamb@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Gary Chambers @ 2007-12-12 16:58 UTC (permalink / raw)
To: pgsql-sql
All...
I have a simple table in PostgreSQL 8.2.5:
CREATE TABLE power_transitions (
-- Transition ID (PK)
tid integer NOT NULL,
-- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery)
sid smallint NOT NULL,
-- Timestamp of transition
statetime timestamp without time zone DEFAULT now() NOT NULL,
-- Is this a real outage?
is_outage boolean DEFAULT true NOT NULL
);
It contains a log of power outages (transitions). I'd like to create
query that returns a transition offline time and associated return to
online time. Is there a better way of handling this? I am open to
schema change suggestions. Thanks very much!
-- Gary Chambers
// Nothing fancy and nothing Microsoft!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Assistance
@ 2007-12-12 17:31 D'Arcy J.M. Cain <darcy@druid.net>
parent: Gary Chambers <gwchamb@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: D'Arcy J.M. Cain @ 2007-12-12 17:31 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gmail.com>; +Cc: pgsql-sql
On Wed, 12 Dec 2007 11:58:20 -0500
"Gary Chambers" <gwchamb@gmail.com> wrote:
> All...
>
> I have a simple table in PostgreSQL 8.2.5:
>
> CREATE TABLE power_transitions (
> -- Transition ID (PK)
> tid integer NOT NULL,
> -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery)
> sid smallint NOT NULL,
> -- Timestamp of transition
> statetime timestamp without time zone DEFAULT now() NOT NULL,
> -- Is this a real outage?
> is_outage boolean DEFAULT true NOT NULL
> );
>
> It contains a log of power outages (transitions). I'd like to create
> query that returns a transition offline time and associated return to
> online time. Is there a better way of handling this? I am open to
> schema change suggestions. Thanks very much!
Have you considered this?
CREATE TABLE power_transitions (
-- Transition ID (PK)
tid integer NOT NULL,
-- Timestamp of power off (
starttime timestamp without time zone DEFAULT now() NOT NULL,
-- Timestamp of power on (
endtime timestamp without time zone,
-- Is this a real outage? may not be needed.
is_outage boolean DEFAULT true NOT NULL
);
The is_outage bool could be handled with special timestamps (e.g.
EPOCH) but I am not sure what it signifies to you. Of course you have
to deal with false transitions but I don't know what your information
capture system is so I can't work out all the details but this seems
like a good base for what you want. Every row that has a valid start
and end time is a complete record of an outage.
I am also thinking of a scheme that uses two tables but I don't really
know your environment or requirements. I am assuming that you spend
more time querying the table than updating it. If not your problem
isn't your database, it's your power plant. :-)
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Assistance
@ 2007-12-12 18:00 Gary Chambers <gwchamb@gmail.com>
parent: D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 7+ messages in thread
From: Gary Chambers @ 2007-12-12 18:00 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
D'Arcy...
> Have you considered this?
I considered integrating the begin and end times into the table. I'm
capturing the data via ACPI events, so it's "transactional" by nature.
I want to be able to keep track of false transitions (hence the
is_outage field).
I'm looking for a way to simplify the transitions output on my web
browser, and I want to combine an offline and a corresponding online
time into a single line.
> I am also thinking of a scheme that uses two tables but I don't really
> know your environment or requirements. I am assuming that you spend
> more time querying the table than updating it. If not your problem
> isn't your database, it's your power plant. :-)
It's nothing complex at all. The power in my neighborhood is
infamously unstable, and I'm merely keep records to complain as much
as I can to the power company.
Thanks for taking the time to reply!
-- Gary Chambers
// Nothing fancy and nothing Microsoft!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Assistance
@ 2007-12-12 20:21 Richard Huxton <dev@archonet.com>
parent: Gary Chambers <gwchamb@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2007-12-12 20:21 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gmail.com>; +Cc: pgsql-sql
Gary Chambers wrote:
> D'Arcy...
>
>> Have you considered this?
>
> I considered integrating the begin and end times into the table. I'm
> capturing the data via ACPI events, so it's "transactional" by nature.
> I want to be able to keep track of false transitions (hence the
> is_outage field).
>
> I'm looking for a way to simplify the transitions output on my web
> browser, and I want to combine an offline and a corresponding online
> time into a single line.
How about a "paired_with" field that references the power_transitions
table and a trigger. When you insert a new row, it checks what the
previous row was - if it's a down & this is an up, then set the
paired_with field on each.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-12-12 20:21 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-04 15:16 query assistance Jodi Kanter <jkanter@virginia.edu>
2003-11-04 15:43 ` Richard Huxton <dev@archonet.com>
2003-11-05 04:09 ` Michael Glaesemann <grzm@myrealbox.com>
2007-12-12 16:58 Query Assistance Gary Chambers <gwchamb@gmail.com>
2007-12-12 17:31 ` Re: Query Assistance D'Arcy J.M. Cain <darcy@druid.net>
2007-12-12 18:00 ` Re: Query Assistance Gary Chambers <gwchamb@gmail.com>
2007-12-12 20:21 ` Re: Query Assistance Richard Huxton <dev@archonet.com>
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