[SQL] unsubscribe
unsubscribe ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check.
[SQL] Unsubscribe
please remove my email from your database contacts. Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] timestamptz - problems
Hi im using 'timestamptz' in a function called: 'getdate' to enter the start and finish time into a db field, however the date appears to be inserted into the db in a random format, i wish for it to only be entered into the db as DD-MM- (European,UK). I understand this is probably a very simple problem to resolve but thus far I have failed, can anyone plz help. 'getdate' function is as follows: *** CREATE FUNCTION getdate() RETURNS timestamptz AS ' BEGIN RETURN now(); END; ' LANGUAGE 'plpgsql'; Inserted using the following function: * CREATE FUNCTION newmess(int4, text, varchar) RETURNS varchar AS ' DECLARE userid ALIAS for $1; message ALIAS for $2; touser ALIAS for $3; enttime DATETIME; touserid INTEGER; rdset BIT; from VARCHAR; BEGIN rdset = 0; touserid=(select id from users where lastname=touser); enttime=(select getdate()); from=(select lastname from users where id = userid); INSERT INTO CallLog.message(message, fromuser, touser, txtime, rd, fromusern) values(message. userid, touserid, enttime, rdset, from); END; ' LANGUAGE 'plpgsql'; * Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Date format problems
Hi im using the function below to insert data into my db; im using now() to get the timestamptz, however when inserted in the db the format seems to vary, the majority of the time its in the required European style but does spontaniously change to various other type can anyone throw any light on this problem. Further info: DATESTYLE is currently set to European. db table type is 'timestamptz' ### CREATE FUNCTION newmess(int4, text, varchar) RETURNS varchar AS ' DECLARE userid ALIAS for $1; message ALIAS for $2; touser ALIAS for $3; enttime DATETIME; touserid INTEGER; rdset BIT; from VARCHAR; BEGIN rdset = 0; touserid=(select id from users where lastname=touser); enttime=(select now()); from=(select lastname from users where id = userid); INSERT INTO CallLog.message(message, fromuser, touser, txtime, rd, fromusern) values(message. userid, touserid, enttime, rdset, from); END; ' LANGUAGE 'plpgsql'; * Im getting desperate, please help if you can, and thx to those that replied to my previous mail. Many Thanks in advance, Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Date format problems
Sure, sorry; Im using postgres version 7.2.1, and the column data type is 'timestamptz' Data examples: 13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or 13/04/02 12:35:27 70% of the time it is inserted in the correct format. The function shown in previous email is called by a C++ Builder 5 program using the postgresSQL ODBC driver version 7.02.00.05 (Insight Distribution Systems) Any other info required ? Sorry for the stupid questions but im a bit of a n00b, no excuse I guess, but I just cant figure out whats going on. Thanks for all your efforts, Kind Regards, Mark. >>> Tom Lane <[EMAIL PROTECTED]> 02/16/04 03:34pm >>> "Mark Roberts" <[EMAIL PROTECTED]> writes: > Hi im using the function below to insert data into my db; im using > now() to get the timestamptz, however when inserted in the db the format > seems to vary, the majority of the time its in the required European > style but does spontaniously change to various other type can anyone > throw any light on this problem. This is way too vague for anyone to help. What PG version are you using? What is the actual datatype of the column you're inserting into? Can you provide a specific example of a misformatted data value? regards, tom lane ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Postgres 8 - Database access, new install.
Hi all, Ive just installed the latest version of Postgres 8 on a RedHat 9 server. The problem im having is than when I try to login to the database i.e. 'psql -U postgres template1' im getting the following message: psql: relocation error: psql: undefined symbol: PQsetErrorVerbosity Upon reading an answer to a previous question this could be cause by a version conflict i.e using old 7.x librarys. So ive checked this, and yes there is another version 7.3 installed which is the default RedHat install. However when checking using 'ldd `which psql` the following libraries are displayed which I believe to be correct: libpq.so.3 => /usr/lib/libpq.so.3 (0x40026000) libpam.so.0 => /lib/libpam.so.0 (0x4003c000) libssl.so.4 => /lib/libssl.so.4 (0x40044000) libcrypto.so.4 => /lib/libcrypto.so.4 (0x40079000) libcom_err.so.2 => /lib/libcom_err.so.2 (0x4016a000) libz.so.1 => /usr/lib/libz.so.1 (0x4016c000) libreadline.so.4 => /usr/lib/libreadline.so.4 (0x4017a000) libtermcap.so.2 => /lib/libtermcap.so.2 (0x401a7000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x401ab000) libresolv.so.2 => /lib/libresolv.so.2 (0x401d8000) libnsl.so.1 => /lib/libnsl.so.1 (0x401ea000) libdl.so.2 => /lib/libdl.so.2 (0x401ff000) libm.so.6 => /lib/tls/libm.so.6 (0x40203000) libc.so.6 => /lib/tls/libc.so.6 (0x4200) libkrb5.so.3 => /usr/kerberos/lib/libkrb5.so.3 (0x40226000) libgssapi_krb5.so.2 => /usr/kerberos/lib/libgssapi_krb5.so.2 (0x40284000) libcom_err.so.3 => /usr/kerberos/lib/libcom_err.so.3 (0x40297000) libk5crypto.so.3 => /usr/kerberos/lib/libk5crypto.so.3 (0x40299000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x4000) Anyways im now desperately stuck, I have 2 versions of postgres install (my mistake), and I dont know how to remove them and start a fresh install because: rpm -qa | grep postg (shows current rpms) postgresql-server-8.0.1-1PGDGpostgresql-8.0.1-1PGDG Does anyone have any idea of how to resolve this problem, I can connect using PGAdminIII but I really would like to be able to login in properly from Command-line also. Many Thx, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. FREEDOM OF INFORMATION ACT 2000: The information contained in this e-mail may be subject to public disclosure under this Act. Unless the information is legally exempt from disclosure, the confidentiality of this e-mail and your reply cannot be guaranteed. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check.
Re: [SQL] Unable to create function which takes no arguments
IIRC, current_timestamp doesn't require parens. You could try something like this: select extract(epoch from current_timestamp)::int4 as result; -Mark On Mon, 2008-06-09 at 12:05 -0400, Michael Eshom wrote: > I am a project manager for a popular forum system. We are adding > support for PostgreSQL in the next version (which is currently in > beta), and have added several PostgreSQL functions to emulate MySQL > functions of the same name. > > I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, > which will return the current timestamp. However, whenever I try to > add this function in phpPgAdmin, it says 'Syntax error at or near ")" > at character 28'. > > This is the SQL I'm using: > > CREATE FUNCTION unix_timestamp() RETURNS integer AS ' > SELECT current_timestamp()::int4 AS result; > ' LANGUAGE SQL; > > The documentation indicates that the arguments are optional, and even > shows an example of a function with no arguments. How can I create > this function? > -- > > > Michael Eshom > Christian Oldies Fan > Cincinnati, Ohio > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Different type of query
On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote: > I would like to have multiple values nutrient_no: > ndb_no | nutrient_no | nutrient_value > +-+ > 13473 | 203 | 24.18 > 13473 | 204 | 15.93 > 13473 | 205 | 0 > 13473 | 207 |1.1 > 13473 | 208 |247 > 13473 | 221 | 0 > > I'm thinking: > select nutrient_no, nutrient_value from nutrient_data where ndb_no = > 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = > 208); > > > Now is that the most efficient SQL query? > > Thanks, > J It seems that you'd want to do something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and nutrient_no in (203, 204, 208..) You could also grab the most significant 8 nutrients by doing something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 order by nutrient_value desc limit 8 -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Fri, 2008-07-11 at 11:43 -0400, samantha mahindrakar wrote: > Hi all > This is a very basic question.can we roll back data after we run a > query. > I know that a delete within a transaction can be rolled back. But how > about independent delete queries??? > If i ran a delete statement and lost data...how do i recover. I know > that oracle has this provision of rollingback queries. > Iam surprised iam not able to find the same in postgres. > > Sam Postgres certainly can roll back queries, table creations, and many other actions. You can find more information about rollback here: http://www.postgresql.org/docs/8.3/interactive/sql-rollback.html Best of luck in your endeavor :) -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to GROUP results BY month
On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
> am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina
> folgendes:
> > Howdy, all,
> >
> > I have a problem.
> >
> > I have a table which one of the fields is of type date.
> >
> > I need to obtain the totals of the other fields in a by-month basis
> > IS there any easy way to do this using the GROUP BY or any other construct?
>
> ... group by extract(month from date)
>
>
> Andreas
It's worth noting that extract(month from timestamp) returns a month_no, and
thus will not be suitable for grouping queries that span years.
I recommend group by date_trunc('month', <>)
-Mark
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to GROUP results BY month
On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote: > am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts > folgendes: > > > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: > > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros > Cristina folgendes: > > > > Howdy, all, > > > > > > > > I have a problem. > > > > > > > > I have a table which one of the fields is of type date. > > > > > > > > I need to obtain the totals of the other fields in a by-month > basis > > > > IS there any easy way to do this using the GROUP BY or any other > construct? > > > > > > ... group by extract(month from date) > > > > > > > > > Andreas > > > > It's worth noting that extract(month from timestamp) returns a > month_no, and thus will not be suitable for grouping queries that span > years. > > Right, but that wasn't the question... Honestly, the way the question was phrased, I'd have assumed that it wanted to group by month (not group by a group of months). Jan 08 is distinct from Jan 07. Please accept my sincerest apologies if you you feel that I misinterpreted the question. I was merely trying to illustrate the difference between what each approach was. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE
On Thu, 2008-07-17 at 12:16 -0400, Alvaro Herrera wrote: > Volkan YAZICI wrote: > > Hi, > > > > What's the difference between below two queue implementations? > > They are two different lock spaces. pg_advisory_lock does not conflict > with regular system locks, whereas LOCK TABLE does. > > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > It also appears that advisory locks are tied to your session, where system locks are tied to your transaction. Also, lock table is a bit more forceful, because it will affect things that don't bother checking advisory locks (such as users, manual scripts, buggy applications, etc). Don't forget that you can use select for update another locking mechanism as well. Well, that's my take on it. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] more than 1000 connections
On Wed, 2008-08-06 at 08:06 +0800, Craig Ringer wrote: > Out of interest - why 1000 connections? > > Do you really expect to have 1000 jobs concurrently active and doing > work? If you don't, then you'll be wasting resources and slowing > things > down for no reason. There is a connection overhead in PostgreSQL - > IIRC > mostly related to database-wide locking and synchronization, but also > some memory for each backend - that means you probably shouldn't run > vastly more backends than you intend to have actively working. > > If you described your problem, perhaps someone could give you a useful > answer. Your mention of pgpool suggests that you're probably using a > web > app and running into connection count limits, but I shouldn't have to > guess that. > > -- > Craig Ringer This is actually a fantastic point. Have you considered using more than one box to field the connections and using some sort of replication or worker process to move them to a master database of some sort? I don't know about the feasibility of it, but it might work out depending on what kind of application you're trying to write. Disclaimer: I work in a data warehousing and we only have 45 concurrent connections right now. OLTP and/or large connection counts isn't really what I spend my days thinking about. ;-) -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, 2008-08-07 at 09:14 -0700, [EMAIL PROTECTED] wrote: >DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ? > This should work for your needs: delete from a using b where a.id = b.id -- join criteria and b.second_id = ? > I have tried to do this before and always found a way, usually > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id > = ?) This is akin to: delete from a where (a.key1, a.key2, a.key3) in (select key1, key2, key3 from b) I use this every day for millions of rows per delete and it works just fine and in a very reasonable time period. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question on partitioning
On Thu, 2008-08-21 at 15:25 +0100, Oliveiros Cristina wrote: > Hello , All. > > I am not sure if this is the right mailing list to place this > question. > If it doesn't, please kindly redirect me to the right list. > > I have a giant table with about 10,6 million records. > > Queries on it are usually slow, and if I try to do something more > elaborate like an INNER JOIN with itself it becomes unnacceptably > slow. > > I am looking for a way to improve performance. > One of the columns is of type date. Each "day" includes about a few > tens of thousands records > And the older a date is the less likely I am to do queries on it. > > The objective of the "self join" is to compare data from two different > days, looking for diferences. > > Ive read that one of the benefits of partitioning is to speed up > queries by separating less used records. > > My question is if partitioning can be a good way to make the queries > faster (specially the self joins) or if it isn't worth trying because > it doesn't help on my particular situation. > > Please kindly advice me on this > > Many thanks in advance for your kind help > > Best, > Oliveiros I would expect partitioning to work. I've heard tell that fine grained partitioning coupled with check constraints can even eliminate the need for certain indexes. I do know that check constraints on the date will help you tremendously if you decide to partition. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RE: [SQL] Why *no* ambig.uous complain in select part?
On Fri, 2008-08-22 at 17:10 -0400, Emi Lu wrote: > > Would you please give me an example? > > I have two tables like the following: > T1 (col1 varchar, col2 varchar, primary key (col1, col2)) > T2 (col1 varchar, col2 varchar, primary key (col1, col2)) > > > Query I have is: > === > select col1, col2 > from T1 > left join T2 using (T1, T2); > > Thanks a lot! If (T1.col1, T1.col2) != (T2.col1, T2.col2) then the join is unsuccessful and T2.col1 and T2.col2 will be null. If you're wondreing if the join was successful: select col1, col2 from T1 left outer join T2 using (col1, col2) where T2.col1 is not null -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion query
On Mon, 2008-09-22 at 16:34 +0200, Louis-David Mitterrand wrote: > > > To select person_type's used in a certain event_type I have this > query: > > select distinct pt.type > from person_type pt > natural join person_to_event > join event e using (id_event) > natural join event_type et > where et.type_fr='théâtre'; > > Now, I'd like to select person_type's _not_ used in a certain > particular > event (say id_event=219). > > I can see how to build a quey to that effect, but is there a more > obvious, clean, short solution? Something that looks like the above > query maybe? Taking your second email into account, I came up with: select distinct pt.type_fr from person_to_event pte inner join person_type using (id_person_type) where id_person_type in ( select id_person_type from person_to_event pte inner join event using (id_event) inner join event_type using (id_event_type) where type_fr = 'theatre' ) and id_person_type not in ( select id_person_type from person_to_event where id_event = 219 ) I feel like there's a solution involving group by tugging at the back of my mind, but I can't quite put my finger on it. Sorry if this isn't quite what you're asking for. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] For each key, find row with highest value of other field
select distinct on (Key) Key, Date, Value
from <>
order by Key, Date desc
MYDATABASE=> create table aaa (key varchar(1), date date, value text);
CREATE TABLE
Time: 1518.002 ms
MYDATABASE=> insert into aaa (key, date, value) values ('A',
'2008-05-01', 'foo');
INSERT 0 1
Time: 1.125 ms
MYDATABASE=> insert into aaa (key, date, value) values ('A',
'2008-04-01', 'bar');
INSERT 0 1
Time: 0.290 ms
MYDATABASE=> insert into aaa (key, date, value) values ('A',
'2008-03-01', 'foo');
INSERT 0 1
Time: 0.310 ms
MYDATABASE=> insert into aaa (key, date, value) values ('B',
'2008-03-01', 'baz');
INSERT 0 1
Time: 0.304 ms
MYDATABASE=> insert into aaa (key, date, value) values ('B',
'2008-02-01', 'bar');
INSERT 0 1
Time: 0.330 ms
MYDATABASE=> insert into aaa (key, date, value) values ('C',
'2008-06-03', 'foo');
INSERT 0 1
Time: 0.298 ms
MYDATABASE=> insert into aaa (key, date, value) values ('C',
'2008-04-04', 'baz');
INSERT 0 1
Time: 0.295 ms
MYDATABASE=> insert into aaa (key, date, value) values ('C',
'2008-03-04', 'bar');
INSERT 0 1
Time: 0.319 ms
MYDATABASE=> commit;
COMMIT
Time: 569.591 ms
MYDATABASE=> select * from aaa;
key |date| value
-++---
A | 2008-05-01 | foo
A | 2008-04-01 | bar
A | 2008-03-01 | foo
B | 2008-03-01 | baz
B | 2008-02-01 | bar
C | 2008-06-03 | foo
C | 2008-04-04 | baz
C | 2008-03-04 | bar
(8 rows)
Time: 0.520 ms
MYDATABASE=> select distinct on (key) key, date, value from aaa order by
key, date desc;
key |date| value
-++---
A | 2008-05-01 | foo
B | 2008-03-01 | baz
C | 2008-06-03 | foo
(3 rows)
Time: 0.524 ms
-Mark
On Sat, 2008-10-04 at 00:25 +0530, Raj Mathur wrote:
> I have some data of the form:
>
> Key | Date | Value
> A | 2008-05-01 | foo*
> A | 2008-04-01 | bar
> A | 2008-03-01 | foo*
> B | 2008-03-04 | baz
> B | 2008-02-04 | bar
> C | 2008-06-03 | foo*
> C | 2008-04-04 | baz
> C | 2008-03-04 | bar
>
> Is there any way to select only the rows marked with a (*) out of
> these
> without doing a join? I.e. I wish to find the row with the highest
> Date for each Key and use the Value from that.
>
> Regards,
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Left Join Question
On Tue, 2008-11-18 at 16:48 -0600, Ryan Wells wrote: > Since it works, my question is really more about principles: Given > that each of the tables in question will contain tens of thousands of > rows, is a nested join really the best way to approach this? I don't see what's wrong with it. The planner will likely pare down tasks to its result set before joining to the other joins (which are all keyed on various fields from task). I know that we have lots of joins like this scattered all over our code, but we usually use inner joins unless there's a specific reason not to. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Aggregates with NaN values
On Thu, 2008-12-04 at 13:01 -0500, Sean Davis wrote: > I am happy to see NaN and infinity handled in input. I would now like > to compute aggregates (avg, min, max, etc) on columns with NaN values > in them. The standard behavior (it appears) is to have the aggregate > return NaN if the data contain one-or-more NaN values. I am used to > using coalesce with NULL values, but that doesn't work with NaN. I > can deal with these using CASE statuement to assign a value, but is > there a standard way of dealing with the NaN (or Infinity, for that > matter) cases to get a behvavior where they are "ignored" by an > aggregate? > > Thanks, > Sean > Have you considered using a where clause? -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
