[SQL] sql

[cristivoinicaru]

I have a postgres table like this:

CREATE TABLE "temp50" (
 "gc" character varying(36),
 "co" character varying(7),
 "data" date,
 "ora" smallint

);

It contains the following records:

5003707G9G9419 22012BB 10-14-2004 82
5003707G9G9419 22012BC 10-14-2004 44
5003707G9G9419 22022BB 10-14-2004 82
5003707G9G9420 22022BC 10-18-2004 49

I'd  like the result of the sql interogation to be like this:

5003707G9G9419 22012BB 10-14-2004 82
5003707G9G9420 22022BC 10-18-2004 49


Explanations:
I want like sql interogation to select only one record from each "gc" group
record (who has the same code "gc" (that means that the "gc" field will be
unique key)) with the following two condition:
1)from each "gc" group of records to select the record who has the value of
"ora" field maxim.
2)if two ore more record have the same value of the maxim value of the "ora"
to select oly one of them

Thanks!


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Cross tabulations

[Muhyiddin A.M Hayat]

Dear,

Thanks, that query is work, so.
So, i would like to calculate total work time




select date, employee_id,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'In') as in,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break Out') as
break_out,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break In') as
break_in,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Out') as out
  from (select distinct employee_id, check_time::date as date from test) as
x;


out - in = work_time






- Original Message - 
From: "Greg Stark" <[EMAIL PROTECTED]>
To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, October 20, 2004 2:35 PM
Subject: Re: [SQL] Cross tabulations


> "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]> writes:
>
> > Dear all,
> >
> > I need to do something similar to a cross tabulation, but without any
> > aggregation.
>
> join your table to itself four times:
>
> select *
>   from (select check_time::date as date, employee_id,
check_time-check_time::date as in from test where state = 'In') as a
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as break_out from test where state = 'Break
Out') as b using (employee_id,date)
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as break_in from test where state = 'Break In')
as d using (employee_id,date)
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as out from test where state = 'Out') as e using
(employee_id,date) ;
>
> Note that this will do strange things if you don't have precisely four
records
> for each employee.
>
> Alternatively use subqueries:
>
> select date, employee_id,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'In') as in,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break Out') as
break_out,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break In') as
break_in,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Out') as out
>   from (select distinct employee_id, check_time::date as date from test)
as x;
>
> This will at least behave fine if there are missing records and will give
an
> error if there are multiple records instead of doing strange things.
>
> Neither of these will be particularly pretty on the performance front.
>
> -- 
> greg
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] sql

[Andrei Bintintan]

> 2)if two ore more record have the same value of the maxim value of the
"ora"
> to select oly one of them

At this point you really have to decide which criteria you use for this
deciding.

I strongly suggest to use a uniqe ID for each row in the table(in your case
it is important):
CREATE TABLE "temp50" (
id serial primary key,
  "gc" character varying(36),
  "co" character varying(7),
  "data" date,
  "ora" smallint
 );

Your selection can be made in many ways, but you have to decide the second
criteria.

One more thing:
Your table datas are:
1 >> 5003707G9G9419 22012BB 10-14-2004 82
2 >> 5003707G9G9419 22012BC 10-14-2004 44
3 >> 5003707G9G9419 22022BB 10-14-2004 82
4 >> 5003707G9G9420 22022BC 10-18-2004 49

The rows no 1 and 3 are "redundant" data's(they are the same). Do you really
want this???

Best regards,
Andy.

- Original Message -
From: "cristivoinicaru" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 25, 2004 11:13 AM
Subject: [SQL] sql


> I have a postgres table like this:
>
> CREATE TABLE "temp50" (
>  "gc" character varying(36),
>  "co" character varying(7),
>  "data" date,
>  "ora" smallint
>
> );
>
> It contains the following records:
>
> 5003707G9G9419 22012BB 10-14-2004 82
> 5003707G9G9419 22012BC 10-14-2004 44
> 5003707G9G9419 22022BB 10-14-2004 82
> 5003707G9G9420 22022BC 10-18-2004 49
>
> I'd  like the result of the sql interogation to be like this:
>
> 5003707G9G9419 22012BB 10-14-2004 82
> 5003707G9G9420 22022BC 10-18-2004 49
>
>
> Explanations:
> I want like sql interogation to select only one record from each "gc"
group
> record (who has the same code "gc" (that means that the "gc" field will be
> unique key)) with the following two condition:
> 1)from each "gc" group of records to select the record who has the value
of
> "ora" field maxim.
> 2)if two ore more record have the same value of the maxim value of the
"ora"
> to select oly one of them
>
> Thanks!
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] bibliographic schema

[James Cloos]

Try googling for:

bibtex sql

That should provide some pointers that may help produce an effective
sql schema for storing bibliographic references.

-JimC
-- 
James H. Cloos, Jr. <[EMAIL PROTECTED]> 

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] sql

[Mike Rylander]

On Mon, 25 Oct 2004 10:13:37 +0200, cristivoinicaru
<[EMAIL PROTECTED]> wrote:
> I have a postgres table like this:
> 
> CREATE TABLE "temp50" (
>  "gc" character varying(36),
>  "co" character varying(7),
>  "data" date,
>  "ora" smallint
> 
> );
> 
> It contains the following records:
> 
> 5003707G9G9419 22012BB 10-14-2004 82
> 5003707G9G9419 22012BC 10-14-2004 44
> 5003707G9G9419 22022BB 10-14-2004 82
> 5003707G9G9420 22022BC 10-18-2004 49
> 
> I'd  like the result of the sql interogation to be like this:
> 
> 5003707G9G9419 22012BB 10-14-2004 82
> 5003707G9G9420 22022BC 10-18-2004 49
> 
> Explanations:
> I want like sql interogation to select only one record from each "gc" group
> record (who has the same code "gc" (that means that the "gc" field will be
> unique key)) with the following two condition:
> 1)from each "gc" group of records to select the record who has the value of
> "ora" field maxim.
> 2)if two ore more record have the same value of the maxim value of the "ora"
> to select oly one of them

Try:

SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC;


--miker

> 
> Thanks!
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] sql

[Andrew J. Kopciuch]

On Monday 25 October 2004 05:20, Mike Rylander wrote:
> SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC;

You can not have have expressions (columns etc.) in the SELECT list that are 
either not in a GROUP BY clause, or used in an aggregate function when you 
use GROUP BY in the statement.  By saying SELECT *, means you would have to 
GROUP BY gc, co, data, ora ...

That isn't going to do what he wants.  And the SQL you stated should give you 
an error:

ERROR:  column "temp50.co" must appear in the GROUP BY clause or be used in an 
aggregate function



What he wants to do is use DISTINCT ON:

SELECT DISTINCT ON (gc) gc, co, data, ora FROM temp50 ORDER BY gc, ora DESC;


Andy

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] sql

[Mike Rylander]

On Mon, 25 Oct 2004 05:44:06 -0600, Andrew J. Kopciuch
<[EMAIL PROTECTED]> wrote:
> On Monday 25 October 2004 05:20, Mike Rylander wrote:
> > SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC;
> 
> You can not have have expressions (columns etc.) in the SELECT list that are
> either not in a GROUP BY clause, or used in an aggregate function when you
> use GROUP BY in the statement.  By saying SELECT *, means you would have to
> GROUP BY gc, co, data, ora ...

Doh!  Of course.   Should have had coffee before writing that ;)

> 
> That isn't going to do what he wants.  And the SQL you stated should give you
> an error:
> 
> ERROR:  column "temp50.co" must appear in the GROUP BY clause or be used in an
> aggregate function
> 
> What he wants to do is use DISTINCT ON:
> 
> SELECT DISTINCT ON (gc) gc, co, data, ora FROM temp50 ORDER BY gc, ora DESC;
> 
> Andy
>

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] now() + integer, not rounded to whole day

[JN]

[EMAIL PROTECTED] (JN) wrote in message news:<[EMAIL PROTECTED]>...
> Is it possible to add some portion of the day to now() ? I cannot
> round now() to whole days like now()::date. Hours, minutes and seconds
> are also important to me. This I had in Oracle, and need to convert it
> :
> 
> sysdate + 1/1440
> 
> Thanks for any suggestion.

It's simpler than I thought 

now() + (TO_TIMESTAMP('1970-01-01 00:01:00', '-mm-dd HH24:MI:SS')
- TO_TIMESTAMP('1970-01-01 00:00:00', '-mm-dd HH24:MI:SS'))

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Finding duplicated values

[Bricklen]

Kent Anderson wrote:
I have a few tables that have duplicated values from an import from a 
different database. I have two keys I tried to set as primary and got an 
error
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
 
Is there some join I can use to compare the hmhmkey, wmwmkey pairs 
against the table to find duplicate values? Each pair key should be 
unique but the old database was less than normalized.
 
I was trying to use the code below but it returned no rows.
 
SELECT hmhmkey, wmwmkey
FROM   exceptions
EXCEPT
SELECT hmhmkey, wmwmkey
FROM  exceptions;
 
Any suggestions?
 
Kent Anderson
EZYield.com
407-629-0900
www.ezyield.com 
Try http://archives.postgresql.org/pgsql-sql/1999-03/msg00239.php
---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] now() + integer, not rounded to whole day

[JN]

Is it possible to add some portion of the day to now() ? I cannot
round now() to whole days like now()::date. Hours, minutes and seconds
are also important to me. This I had in Oracle, and need to convert it
:

sysdate + 1/1440

Thanks for any suggestion.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] [GENERAL] Log

[Scott Marlowe]

On Fri, 2004-10-22 at 07:52, Davide Negri wrote:
> Hello, 
> 
> i have installed the 8.0 beta3-dev1 version of postgres on my pc, and
> I want to disable log. How can I do? It is possible to disable and
> donât write all the log, or how can I write all the log in a specific
> directory??

If you use pg_ctl to start postgresql, then edit the
$PGDATA/postgresql.conf file and set 

#silent_mode = false

to be

silent_mode = true

and restart your database.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Question on the 8.0Beta Version

[Davide Negri]

Hello, 

i have installed postgres 8.0 beta3 as a program on
my pc on windows xp. I have read on the installation note file that postgres
server will not run with administrative permissions. I have read on your forum
that this happened because i have installed the beta version: is it true that
this happen only with the beta version? When will you plan to release the
production version? 

 

Thanks You

 

Negri Davide








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004
 

[SQL] "copy from" in "create function"

[Gifford Hesketh]

Am I forgetting some limitation ?  I get "ERROR:  syntax error at or
near "$1" at character 22" with this:

CREATE FUNCTION public.fn_b_import( text ) RETURNS void AS 
'
COPY b_import FROM $1 ;
'
LANGUAGE 'sql' STABLE;

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Aggregate Function with Argument

[David Siegal]

Mark,
  Works beautifully. Thanks for the clear explanation and code!
-David

On Tue, 19 Oct 2004, Mark Gibson wrote:

> David Siegal wrote:
> > I would like to create an aggregate function that returns a concatenation
> > of grouped values. It would be particularly useful if I could pass an
> > optional delimiter into the aggregate function.
>
> I've managed to do this in two stages:
>
> 1. Collect the set of values into an array.
> This can be done using a custom aggregate function, array_accum,
> which is demonstrated within the PostgreSQL manual:
> http://www.postgresql.org/docs/7.4/interactive/xaggr.html
>
> But here it is again:
>
> CREATE AGGREGATE array_accum (
>   sfunc = array_append,
>   basetype = anyelement,
>   stype = anyarray,
>   initcond = '{}'
> );
>
> It makes me wonder why this isn't a built-in aggregate???
>
> 2. Convert the array to a string.
> Using the built-in function array_to_string:
> http://www.postgresql.org/docs/7.4/interactive/functions-array.html
>
> Example:
>
>SELECT
>  team_number,
>  array_to_string(array_accum(member_name), ', ') AS members
>FROM team
>GROUP BY team_number;
>
>
> You can also go full round-trip (delimited string -> set) using the
> builtin function: string_to_array, and a custom pl/pgSQL function:
>
> CREATE FUNCTION array_enum(anyarray) RETURNS SETOF anyelement AS '
>DECLARE
>  array_a ALIAS FOR $1;
>  subscript_v integer;
>BEGIN
>  FOR subscript_v IN array_lower(array_a,1) .. array_upper(array_a,1)
>  LOOP
>RETURN NEXT array_a[subscript_v];
>  END LOOP;
>  RETURN;
>END;
> ' LANGUAGE 'plpgsql'
> STRICT IMMUTABLE;
>
> Example:
>
>SELECT * FROM array_enum(string_to_array('one,two,three',','));
>
> --
> Mark Gibson 
> Web Developer & Database Admin
> Cromwell Tools Ltd.
> Leicester, England.
>
>

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Log

[Davide Negri]

Hello, 

i have installed the 8.0 beta3-dev1 version of
postgres on my pc, and I want to disable log. How can I do? It is possible to
disable and don’t write all the log, or how can I write all the log in a
specific directory??

 

Thanks

 

Negri Davide








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004
 

Re: [SQL] now() + integer, not rounded to whole day

[Bruno Wolff III]

On Mon, Oct 25, 2004 at 02:27:01 -0700,
  JN <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] (JN) wrote in message news:<[EMAIL PROTECTED]>...
> > Is it possible to add some portion of the day to now() ? I cannot
> > round now() to whole days like now()::date. Hours, minutes and seconds
> > are also important to me. This I had in Oracle, and need to convert it
> > :
> > 
> > sysdate + 1/1440
> > 
> > Thanks for any suggestion.
> 
> It's simpler than I thought 
> 
> now() + (TO_TIMESTAMP('1970-01-01 00:01:00', '-mm-dd HH24:MI:SS')
> - TO_TIMESTAMP('1970-01-01 00:00:00', '-mm-dd HH24:MI:SS'))

You probably wanted to do something like: now() + '1 hour'::interval

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] "copy from" in "create function"

[Jan Wieck]

On 10/20/2004 6:03 PM, Gifford Hesketh wrote:
Am I forgetting some limitation ?  I get "ERROR:  syntax error at or
near "$1" at character 22" with this:
CREATE FUNCTION public.fn_b_import( text ) RETURNS void AS 
'
COPY b_import FROM $1 ;
'
LANGUAGE 'sql' STABLE;
Utility statements don't accept parameters for arguments. Use the 
EXECUTE syntax of PL/pgSQL for this.

Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)

[Marinos Yannikos]

Hi,
I'm probably misintepreting the documented equivalence of PERFORM and 
SELECT INTO as far as the special variable FOUND is concerned, but the 
following 2 definitions do not seem to produce the same result.

create table blup ( t1 text, t2 text );
create function blup_unique2 (text,text) returns boolean as 'begin 
perform (select 1 from blup where t1=$1 or t1=$2 or t2=$1 or t2=$2 or 
$1=$2 limit 1); return NOT FOUND; end' LANGUAGE plpgsql;

create function blup_unique3 (text,text) returns boolean as 'declare x 
record; begin select into x 1 from blup where t1=$1 or t1=$2 or t2=$1 or 
t2=$2 or $1=$2 limit 1; return NOT FOUND; end' LANGUAGE plpgsql;

The first will always produce false, i.e. apparently the subquery used 
by PERFORM will not set the variable FOUND as expected. Is this correct?

(btw.: I'm trying a few ways to ensure that all values in both t1 and t2 
are unique:
alter table blup add constraint check (blup_unique3(t1,t2));
 - perhaps there are more elegant ways, any suggestions?)

Regards,
 Marinos
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND

[Edmund Bacon]

Marinos Yannikos wrote:
(btw.: I'm trying a few ways to ensure that all values in both t1 and t2 
are unique:
alter table blup add constraint check (blup_unique3(t1,t2));
 - perhaps there are more elegant ways, any suggestions?)

No doubt someone will tell me this is Very Wrong:
create table blup_text(txt text primary key);
create table blup (t1 text references blup_text(txt),
   t2 text references blup_text(txt));
You may want to add NOT NULL conditions to t1, t2.
If your text values are long, this may be a good place to use synthetic 
keys - e.g.

create table blup_text(id serial primary key, txt text unique);
create table blup(t1 integer references blup_text(id),
  t2 integer references blub_text(id));

--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)

[Tom Lane]

Marinos Yannikos <[EMAIL PROTECTED]> writes:
> create function blup_unique2 (text,text) returns boolean as 'begin 
> perform (select 1 from blup where t1=$1 or t1=$2 or t2=$1 or t2=$2 or 
> $1=$2 limit 1); return NOT FOUND; end' LANGUAGE plpgsql;

You've got a syntax problem.  PERFORM is syntactically like SELECT,
so what you wrote is equivalent to
SELECT (SELECT 1 FROM blup )
In other words, you are evaluating a scalar subquery, which is going to
return either "1" or "NULL" depending on whether the WHERE matches,
or give an error if the WHERE matches multiple rows (a case you wouldn't
hit because of the LIMIT).  So the outer SELECT produces exactly one row
containing the scalar result, and FOUND always ends up TRUE.

So what you want is just

PERFORM 1 FROM blup ...

and then check the FOUND result from that.

(The 8.0 docs hopefully explain this more clearly; PERFORM was
certainly not very well documented before.)

Note that I'm concerned that the performance of this will suck ...
in particular you really ought to test the $1=$2 case separately.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org