Re: [SQL] simple join is beating me

query here */) ordersQuery NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery ORDER BY deliveryQuery.o_date DESC Tararabite, Oliveiros @Allgarve 2009/7/13 Gary Stainburn > hi folks > > i have the following: > > select o_ord_date as o_date, count(o_id) as orders >from

Re: [SQL] simple join is beating me

place the RIGHT by FULL Best, Oliveiros 2009/7/13 Gary Stainburn > Hi Oliveiros, > > Thank you for this. However, this does not give me what I want. > > If a date exists where we have orders but no deliveries the row does not > appear. > > I have tried doing a union to lin

Re: [SQL] UNION or LEFT JOIN?

7;t have a register for that pair (îd_cabin,id_cruise), how do you know that cabin belongs to that cruise, in this case, cruise nº 1? Best, Oliveiros 2010/2/16 Louis-David Mitterrand > > Here is the basic schema: > >

Re: [SQL] check constraint on multiple tables?

rop the relation between type and category. The cabin table would then work as an associative table between category and type. Ain't saying your schema is wrong, maybe you have strong reasons to do that that way, that I am not realizin by now... Best, Oliveiros d'Azevedo Cristina -- S

Re: [SQL] How to max() make null as biggest value?

this is "air code", but if you use count on a column with nulls the return value is different from count(*) HTH Best, Oliveiros Cristina - Original Message - From: "Feixiong Li" To: Sent: Wednesday, April 14, 2010 5:33 AM Subject: [SQL] How to max() make null as

Re: [SQL] LEFT OUTER JOIN issue

Hi, Thomas. I believe it is because of your WHERE clause, which is filtering out the nulls from hp table. According to WHERE hp.poste_idposte = 275 You only want registers that have hp.poste_idposte = 275, not the null ones. HTH Best, Oliveiros - Original Message

Re: [SQL] LEFT OUTER JOIN issue

, ct.heur And drop the WHERE clause. See if it gives the results you intended. Best, Oliveiros - Original Message - From: Oliveiros To: Thomas BOURIMECH ; pgsql-sql@postgresql.org Sent: Wednesday, April 21, 2010 1:53 PM Subject: Re: [SQL] LEFT OUTER JOIN issue Hi

Re: [SQL] how to construct sql

Hi, Have you already tried this out? select MAX(page_count_count) - MIN(page_count_count) from page_count group by page_count_pdate. Best, Oliveiros - Original Message - From: "Wes James" To: Sent: Wednesday, June 02, 2010 5:48 PM Subject: [SQL] how to construct s

Re: [SQL] Select and merge rows?

Howdy! Is there a maximum ceilling of three values per order ID? or an ID can have an arbitrary number of values? Best, Oliveiros 2011/5/5 Claudio Adriano Guarracino > Hello! > I have a doubt about a query that I tried to do, but I cant.. > This is the scenario: > I have a

Re: [SQL] sql query problem

What does a 0 state mean? Failed? And a 1 state? Passed? Best, Oliveiros 2012/1/14 Alok Thakur > Dear All, > > I have two tables one contains details of user and other contains > result. The details are: > 1. UserTable - id, name, phone > 2. result - id, question_id, user_

Re: [SQL] Lowest 2 items per

Nice resource, msi77. Thanx for sharing. I wasn't aware of none of these techniques, actually, so I tried to start from scratch, but I should've realized that many people in the past had the same problem as Mike and I should have googled a little instead of trying to re-invent the wheel. Anyway,

Re: [SQL] Composite primary keys

error informing that primary key constraint is being violated. But IMHO the first INSERT is legal SQL.... Best, Oliveiros - Original Message - From: "Harald Fuchs" To: Sent: Tuesday, June 23, 2009 4:14 PM Subject: [SQL] Composite primary keys I tried to throw some invalid S

Re: [SQL] Bucketing Row Data in columns

are fixed at 3. So, you mean the table output will always have 4 columns? 3 days plus one for user_id ? If you want 20 buckets it must be a different query... Could you please clarify what you mean when you say that you want to get a bucket unlimited ? Best, Oliveiros - Original Messag

Re: [SQL] Bucketing Row Data in columns

l) Getting the counts takes half the time, making the flattened report takes half the time. Oliveiros Cristina wrote: Hello, Sandeep, I am not sure if this is what you want. I came up with this query SELECT * FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE create_timestamp = '2

[SQL] Differences between bit string constant sintax

.7, compiled by Visual C++ build 1400" Thanx in advance for your help Best, Oliveiros

Re: [SQL] simple (?) join

, orders.next_field, etc... Best, Oliveiros - Original Message - From: "Gary Stainburn" To: Sent: Thursday, September 24, 2009 4:16 PM Subject: [SQL] simple (?) join Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id seri

Re: [SQL] simple (?) join

Hmm...no, it seems, it is not allowable to use orders.* on a GROUP BY clause. Unless you've defined for the table something called an ordering operator. If you didn't, you'll have to include all the fields from the orders table in the GROUP BY clause HTH Best, Oliveiros

Re: [SQL] simple (?) join

group by o_id because the MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably the parser will complain that column ol_timestamp does not exist. Ain't I right? Best, Oliveiros - Original Message - From: justin To: David W Noon Cc: pgsq

Re: [SQL] simple (?) join

Hello, Gary. thank you for your e-mail This is a slightly modified version of the query I sent you on first place (have you tried it out?). It will return (I hope :) the full orders record plus the maximum ol_timestamp and respective o_user. HTH Best, Oliveiros SELECT subquery

[SQL] How to order varchar data by word

Hello, list. I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres. Does anyone have a clue on how this can be done? Many thanx in advance, Best, Oliveiros

Re: [SQL] How to order varchar data by word

(zero). According to ASCII numeric codes, it shouldn't If any one can explain me exactly how the order by clause works on varchars, I 'd really appreciate it. I've already examined documentation carefully, but couldn't find it. Maybe I looked on the wrong place... Best,

Re: [SQL] How to order varchar data by word

Best, Oliveiros - Original Message - From: "Tom Lane" To: "Oliveiros C," Cc: "Adrian Klaver" ; Sent: Monday, October 19, 2009 6:32 PM Subject: Re: [SQL] How to order varchar data by word "Oliveiros C," writes: If any one can explain me exact

Re: [SQL] How to order varchar data by word

Hello, Adrian, thanks for the links. I enjoyed the technique on the end of the second page (on the version 8.3 with comments), the shadow column trick. Given that LC_COLLATE is immutable, unless a new database is created, I guess I'll give the shadow column a try. Best, Oliv

Re: [SQL] need nelp with aggregate functions

Try substituting the SELECT count(c) as qtd_client,count(cm) as qtd_computers by SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the primary key of the computer table */ ) as qtd_computers Then tell me if it output what you want Best, Oliveiros - Original Message

Re: [SQL] need nelp with aggregate functions

appearing more than once... It's basically that... Hope this helped Best, Oliveiros - Original Message - From: Another Trad To: Oliveiros C, Cc: pgsql-sql@postgresql.org Sent: Wednesday, November 18, 2009 5:37 PM Subject: Re: [SQL] need nelp with aggregate functions

[SQL] Is there any function to test for numeric ips?

ow very little ( nothing, actually...) about function creation in pgplsql, so, before investing in learning, I would like to know if there is any built in function that does the trick, so I can skip the (potential) learning curve. So far, I've googled up and down but found none Thanx in advance for your kind and valuable help Best, Oliveiros

Re: [SQL] Is there any function to test for numeric ips?

depth subjects like pgplsql not to mention regular expressions Thanx a lot for your fast help, Best, Oliveiros - Original Message - From: "Alvaro Herrera" To: "Oliveiros C," Cc: Sent: Friday, December 11, 2009 4:28 PM Subject: Re: [SQL] Is there any functio

Re: [SQL] selecting rows tagged with "a" but not "b"

o give you more advises Thank you Best, Oliveiros - Original Message - From: <8q5tmky...@sneakemail.com> To: Sent: Monday, February 01, 2010 1:31 PM Subject: [SQL] selecting rows tagged with "a" but not "b" Hi, I have a two tables: article articleID, na

Re: [SQL] "left join" not working?

My first guess is that NULL fails the condition on your WHERE clause, p.id_line = 1 So your WHERE clause introduces an additional level of filtering that filters out the NULLs coming from the LEFT JOIN... Didn't do any tests, it's just a guess... Best, Oliveiros - Origin

Re: [SQL] "left join" not working?

Yes, because your WHERE is something that comes after the operation of the LEFT JOIN, in practice, defeating the purpose you intented. On your second query the p.id_line = 1 doesn't do that because it is part of the LEFT JOIN itself.. HTH Best, Oliveiros d'Azevedo Cristina

Re: [SQL] JOIN

t for ex for , CRIS | +40741775622 ? And For LIA | +40741775621 ? Thanx in advance Best, Oliveiros 2007/6/5, Loredana Curugiu <[EMAIL PROTECTED]>: Any help, please? On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote: > > Hi everybody, > > I have the follo

Re: [SQL] JOIN

bout the result Best, Oliveiros 2007/6/5, Loredana Curugiu <[EMAIL PROTECTED]>: On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote: > > > > On 6/5/07, Oliveiros Cristina < [EMAIL PROTECTED]> wrote: > > > > Hey, Loredana. > > > Hi Oliveiros!

Re: [SQL] JOIN

remaining two Cheers, Oliveiros - Original Message - From: Loredana Curugiu To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org Sent: Tuesday, June 05, 2007 3:15 PM Subject: Re: [SQL] JOIN On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:

Re: [SQL] JOIN

,date FROM my_table WHERE date=ANY(dates) GROUP BY theme,receiver,date ; But I don't know, do you need to include the column "dates" on output ? Best, Oliveiros From: Loredana Curugiu To: Oliveiros Cristina ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org Sent: Tuesd

[SQL] Obtaining the primary key of the record just inserted

Hello, All, I have the need to know the primary key assigned to a record I've just INSERTed . Is there an easy way to solve this ? Similar to SQLServer's SELECT scope_identity() ; ? Any help deeply appreciated Best, Oliveiros

Re: [SQL] Obtaining the primary key of the record just inserted

It worked perfectly. Thanks a million, Scott. Best, Oliveiros - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "Oliveiros Cristina" <[EMAIL PROTECTED]> Cc: Sent: Thursday, November 08, 2007 6:03 PM Subject: Re: [SQL] Obtaining th

Re: [SQL] This SQL works under Mysql, not Postgresql.

Neither have I. The LEFT JOIN I know is something like SELECT ... FROM table1 LEFT OUTER JOIN table2 ON Try using this construct Best, Oliveiros - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "acec acec" <[EMAIL PROTECTED]&g

[SQL] How to GROUP results BY month

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? Thanks in advance for your kind help Best, Oliveiros -- Sent

Re: [SQL] How to GROUP results BY month

tips on indexing Again, thanks a lot. Best, Oliveiros - Original Message - From: "Mark Roberts" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, July 16, 2008 5:29 PM Subject: Re: [SQL] How to GROUP results BY month On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrot

Re: [SQL] How to GROUP results BY month

Still another way to do :-) Thanks, Lennin. Best, Oliveiros - Original Message - From: "Lennin Caro" <[EMAIL PROTECTED]> To: ; "A. Kretschmer" <[EMAIL PROTECTED]> Sent: Friday, July 18, 2008 3:04 PM Subject: Re: [SQL] How to GROUP results BY month

Re: [SQL] Accessing array datatype

what you need...? Best, Oliveiros - Original Message - From: maria s To: Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ; Ivan Sergio Borgonovo Sent: Monday, July 28, 2008 5:09 PM Subject: [SQL] Accessing array datatype Hi All, I have an array

Re: [SQL] Accessing array datatype

table, but I don't know if that's your goal Also, Please don't send private replies, always include the mailing list address because someone with more knowledge than me might be able to quickly help you ;-) Best, Oliveiros - Original Message - From: maria s

Re: [SQL] column names with - and (

Maria, The minus, open bracket and close bracket are illegal as identifier names http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Best, Oliveiros - Original Message - From: maria s To: Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org

Re: [SQL] column names with - and (

You can enclose the names in quotes, then the characters are allowed Sorry to mislead you :p Best, Oliveiros - Original Message - From: Oliveiros Cristina To: maria s ; Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ; Ivan Sergio Borgonovo Sent: Tuesday

Re: [SQL] Join question

b.user OR (a.user is null and b.user is null ))) Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ... I know This is not a very elegant solution, but seems to give the results you need Best, Oliveiros -

Re: [SQL] Join question

Already tried making two queries and then outputting the UNION of the results? The second one could be something like this... SELECT org,null,COUNT(color) FROM b WHERE user IS NULL AND org = 'a' GROUP BY org Best, Oliveiros - Original Message - From: Edward W. Rouse

[SQL] Question on partitioning

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

Re: [SQL] exclusion query

ditions to the commented line (ORed ) Best, Oliveiros select distinct pt.type from person_type pt natural join person_to_event join event e using (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 -- put here the id of the event you wanna exclude join event_

Re: [SQL] Finding sequential records

Can this be what you need? Best, Oliveiros SELECT id FROM dummy a NATURAL JOIN ( SELECT fkey_id,name FROM dummy GROUP BY fkey_id,name HAVING COUNT(*) > 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) + 1) / 2 ) b ORDER BY id; - Original Message - From: "Steve

Re: [SQL] Finding sequential records

e that clause by something simpler, like MAX(id) - MIN(id) = 1 I dunno if I fully answered your questions, but if I didn't feel free to ask Best, Oliveiros > > -- We are going to have peace even if we have to fight for it. - General Dwight D. Eisenhower Teremos paz, nem que tenhamos

Re: [SQL] optimizing a query over tree-like structure

. I am not very specialized in optimizing queries, but I see you have a lot of cartesian products on your FROM clause, which, from my own experience, I guess it has tendency to be slow... Best, Oliveiros - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, September 30,

Re: [SQL] optimizing a query over tree-like structure

or P, just with an F. Which info is to be retrieved, exactly in this case ? Best, Oliveiros - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, September 30, 2008 3:24 PM Subject: Re: [SQL] optimizing a query over tree-like structure another idea i just got, to dec

Re: [SQL] For each key, find row with highest value of other field

Howdy, Raju Do you want somethin like this? Not sure if I fully understood your requests... SELECT "Key",MAX(Date) FROM t_yourTable WHERE Value LIKE 'foo' GROUP BY Key ; Best, Oliveiros - Original Message - From: "Raj Mathur" <[EMAIL PROTECTED]>

Re: [SQL] sequence number in a result

Best, Oliveiros "(SELECT resumo1.\"iPages\",resumo1.\"iUniqueVisitors\",resumo1.\"IDSite\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\", " + "COUNT(resumo2.\"iPages\") as rank " + "FROM " + m_strSUB

Re: [SQL] sequence number in a result

Sorry, Lance By mistake I posted my own things, which I was using to "adapt" to your situation. :-) Please kindly ignore everything below my signature :p Best, Oliveiros - Original Message - From: Oliveiros Cristina To: Campbell, Lance ; pgsql-sql@postgresql.org Sent

Re: [SQL] Search fields in multiple tables

Howdy, Raju. Could you please provide a more concrete example of what you need? What information would you like your query to return exactly? An ordered set of records? Ordered by rank...? Please give an example of a search operation and intended result Best, Oliveiros - Original

Re: [SQL] Postgres-sql-php

lei 10 mike 5 2manuel child135 child233 child3 30 Confirm, please . Also, do you want the output ordered by age? always? If so , tell me and we can tweak a little the query to best-fit your needs Best, Oliveiro

Re: [SQL] Postgres-sql-php

the first condition on the right outer join coz it would give trouble if two childs happened to be of the same age. I guess it will never happen two childs with the same name :p Best, Oliveiros SELECT a.num,a.father,b.child,b.age FROM ( SELECT num,father, MIN(child)as primeira FROM t1 GROUP BY

Re: [SQL] Postgres-sql-php

I guess you can change a little the query to your needs. The problem is pretty much the same... I've used c3 column in equality, but if this column has repeated values, just choose any column or combination of columns which is unique. Best, Oliveiros SELECT a.c1,a.c2,b.c3,b.c4,a.c5,b.c6

Re: [SQL] grouping/clustering query

Your script is handy, Steve. Spontaneously, This seems to be an array type problem, something I just have vague notions about. I'll take a look at this, http://www.postgresql.org/docs/8.3/static/arrays.html to see if something occurs... Best, Oliveiros - Original Message -

Re: [SQL] Performing intersection without intersect operator

Howdy, Nacef, Try this, SELECT a.userid FROM orders a JOIN orders b USING (userid) WHERE a.productid = 1 AND b.productid = 2 Best, Oliveiros - Original Message - From: Nacef LABIDI To: pgsql-sql@postgresql.org Sent: Tuesday, October 28, 2008 11:14 AM Subject: [SQL

Re: [SQL] Subqueries

alias v not visible in sub-query? - Original Message - From: Pascal Tufenkji To: pgsql-sql@postgresql.org Sent: Thursday, October 30, 2008 12:17 PM Subject: [SQL] Subqueries Hello, I don't understand the following error. Can anyone help me plz Thx Pascal

Re: [SQL] Subsorting GROUP BY data

what you need (with minor fixes...) Best, Oliveiros - Original Message - From: "Johnson, Michael L." <[EMAIL PROTECTED]> To: Sent: Monday, November 10, 2008 2:56 PM Subject: [SQL] Subsorting GROUP BY data Given the following table: ID | Cat | Num |---|---

Re: [SQL] function - string ends with

If I understand what you need, I guess this clause does work. WHERE string LIKE '%substring' Best, Oliveiros - Original Message - From: Kevin Duffy To: pgsql-sql@postgresql.org Sent: Friday, November 21, 2008 4:30 PM Subject: [SQL] function - string ends with

Re: [SQL] Best way to "and" from a one-to-many joined table?

Howdy, Bryce Could you please try this out and tell me if it gave what you want. Best, Oliveiros SELECT person_name FROM test_people p JOIN test_attributes a ON ((a.people_id = p.people_id) AND (a."attribute" = @firstAttr)) JOIN test_attributes b ON ((b."people_id" = p

Re: [SQL] Best way to "and" from a one-to-many joined table?

, not just with postgres, but also with other sgbd , I can tell that subqueries of the kind WHERE x in (SELECT ... ) have the tendency to be slow, that's why I tried to provide you a solution with the JOINs Best, Oliveiros 2008/12/5 Bryce Nesbitt <[EMAIL PROTECTED]> > It works

Re: [SQL] Best way to "and" from a one-to-many joined table?

*How can I get an AND search (people with Dark Hair AND who are President)?* The two joins didn't work? Or were they too slow ? Best, Oliveiros 2008/12/10 Bryce Nesbitt <[EMAIL PROTECTED]> > > > Milan Oparnica wrote: > > This is how I do it, and it runs fas

Re: [SQL] I need some magical advice

Andreas, Does your table has any field that can be used as primary key? Any "ID" field? Best, Oliveiros - Original Message - From: "Andreas" To: Sent: Thursday, January 29, 2009 11:56 AM Subject: [SQL] I need some magical advice Hi, I'd like to updat

Re: [SQL] I need some magical advice

st in plain SQL I couldn't find none :-( Best, Oliveiros UPDATE t_your_table SET status_id = -1 WHERE id IN( SELECT id FROM t_your_table a JOIN ( SELECT c.name,MAX(c.status_id) as estado,MIN(d.oldest) as oldest FROM t_your_table c JOIN( SELECT name,MIN(c_date) as oldest FROM t_your_table WHERE

Re: [SQL] Grass Root Protectionism

Just follow Google's motto "Don't be evil" :-) We are all engineers from the same field, so Let's all be friends Best, Oliveiros - Original Message - From: Boycott Tech Forums To: pgsql-sql@postgresql.org Sent: Sunday, February 08, 2009 6:40 AM

Re: [SQL] uso de dblink en php

Escribe tu duda en ingles, por favor Saludos, Oliveiros - Original Message - From: Jhonny Velasquez c. To: pgsql-sql@postgresql.org ; pgsql-...@postgresql.org Sent: Thursday, March 05, 2009 3:50 PM Subject: [SQL] uso de dblink en php hola a todos estoy programando

[SQL] Re: [SQL] Relatorio da composiçao de FKs e PKs

Escreve a tua dúvida em inglês, por favor, isto é uma lista de mailing em ingles. He wants to do a report with the composition of each foreign key and primary key, not just their names. He wants to know where postgres keeps that in catalog. Best, Oliveiros - Original Message

[SQL] performance question

by the former condition) Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below). I have an index on all the fields used in the query. Can anyone help me in fixing this, please? Thanks in advance for your kind

Re: [SQL] Need help combining 2 tables together

I guess this is pretty much the same as doing SELECT message.id,topic.topic,message.message FROM topics JOIN messages ON topics.id = message.topic ORDER BY message.ID Ain't I right? Best, Oliveiros - Original Message - From: James Kitambara To: Richard Ekblom Cc:

[SQL] Obtaining a limited number of records from a long query

Dear List, Is there any way to force a query to return just a few records? For ex, returning just the first ten rows from a long ORDER BY query ? And then, eventually, obtaining the remaining records at a later time, or in background? Thanks in advance for your help, Best, Oliveiros

Re: [SQL] Obtaining a limited number of records from a long query

Thanks a million, Andy. It's precisely what I needed, Indeed! Best, Oliveiros - Original Message - From: "Andy Shellam" To: "Oliveiros Cristina" Cc: Sent: Monday, May 25, 2009 2:45 PM Subject: Re: [SQL] Obtaining a limited number of records from a l

Re: [SQL] Obtaining a limited number of records from a long query

Thank you for pointing it out, Ivan. The query I am trying to restrict output for happens to be an ORDER BY query. Actually I wasn't aware of this detail. I'll leave a mental note for myself that results might be unexpected for not ORDER BY queries Best, Oliveiros - Origin

Re: [SQL] sum an alias

); Maybe I 'm misunderstanding the background of what you want to do Best, Oliveiros -- 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] Some insight on the proper SQL would be appreciated

Howdy, Aaron, For me this is not an easy question, specially when I don't have your data here on my PC to test. But as a first approach, try this query. It is designed to give you the oldest 5 entries. But, be aware that this is non-tested code. Be prepared for it to not work or even to cont

Re: [SQL] Outer join construction step :strange results

ndition always truth ? Maybe I am lacking some sql knowledge ... Thanks in advance for advising me Best, Oliveiros -- 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] Outer join construction step :strange results

Oliveiros d'Azevedo Cristina wrote: Howdy, Cedric . Before analysing this problem of yours further, I 'd like you to kindly clarify me some points, please I have table following tables T0,T1,T2,T3, T1T2 with -T0(id, fk_T1) -T1(id ) -T2(id, fk_T3) -T1T2(fk_T1,fk_T2) -T3 (id)

Re: [SQL] Outer join construction step :strange results

forget to CC to list, it's always possible that someone with more knowledge than me might help you faster Best, Oliveiros -- 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] grouping subsets

Howdy, Rainer. Please advice me, The dates always follow that sequential pattern? Or can be holes on the dates sequence? Best, Oliveiros - Original Message - From: "Rainer Stengele" To: Sent: Thursday, July 22, 2010 9:09 AM Subject: [SQL] grouping subsets Hi, havi

Re: [SQL] grouping subsets

ou have several thousands of records on your table it will become slow... Best, Oliveiros SELECT SUM(tudo.parcela),tudo.a FROM ( SELECT fo.parcela,fo.a,fo.b,fo.c,MIN(th.c) as d FROM ( SELECT se.a as parcela,se.b as a,se.c as b, MAX(pr.c) as c FROM yourTable se LEFT JOIN ( SELECT a.* FROM y

Re: [SQL] grouping subsets

e day with several sets? Best, Oliveiros - Original Message - From: "Rainer Stengele" Newsgroups: gmane.comp.db.postgresql.sql To: "Oliveiros d'Azevedo Cristina" Cc: <> Sent: Thursday, July 29, 2010 10:41 AM Subject: Re: grouping subsets Howdy Cristina,

Re: [SQL] grouping subsets

Fine. Please advice me, How long can your table be? Thousands? Millions of records? Do you really need it in pure SQL ? It seems to me that it might be possible, I'm just affraid that the query would become too complex and thus slow... Best, Oliveiros - Original Message -

Re: [SQL] grouping subsets

I See. And the analysis you need to do, the sum of the rows with the same keys (until they change) will have to be done over all table? Or just over some predefined interval ? Best, Oliveiros - Original Message - From: "Rainer Stengele" Newsgroups: gmane.comp.db.postgre

Re: [SQL] join table problem

this (untested) query a try SELECT name,MIN(company_name) FROM plant a JOIN plant_seed_supplier associative ON plant_id = a.id JOIN seed_supplier b ON b.id = supplier_id GROUP BY name Best, Oliveiros -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your

Re: [SQL] Extract created and last modified data

Howdy, Gordon. This query is an attempt to replicate your items table with data just from changelog table. Could you please try it out? Best, Oliveiros SELECT itm_id,usr_id_create, itm_date_create, usr_id_modify, itm_date_modify FROM ( SELECT a.itm_id, a.usr_id as usr_id_create, create as

Re: [SQL] all the table values equal

Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B-- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Best, Oliveiros Cristina - Original Message

Re: [SQL] all the table values equal

time interval? Is my understanding correct? Best, Oliver - Original Message - From: "Michele Petrazzo - Unipex" To: "Oliveiros d'Azevedo Cristina" Cc: Sent: Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table values equal Oliveiros d'

Re: [SQL] all the table values equal

It would help if you explain a little better the background of the problem you're trying to solve. You want to find all the user IDs which have the same value on a given time interval? Is my understanding correct? Yes. Re-reading my post I saw that I could explain better! id_user | value

Re: [SQL] all the table values equal

d the clause "WHERE "value" = x -- x=the specific value you want to look for to the query above. It'll return just the id_user(s) that have just the "value" x. Please try it out and feel free to get back to me if it didn't work or if it didn't solve your

Re: [SQL] all the table values equal

Yes. All the users that have only one value into the "value" column and that value is NN. Understood. Try the query from the previous mail, adding the clause WHERE "value" = NN as I did explain. Or will you need to find all the users that don't change value with just one query? And the

Re: [SQL] Help with queries.

Howdy, Adrian, Please see my questions below snps table id | sample_id | chromosome | from | to | 1 1chr1 10 11 2 1 chr1 14 15 3 2

Re: [SQL] unique fields

Howdy, Adrian Dunno if this is exactly what you want SELECT * FROM ( SELECT chr,cfrom,cto,count(*) as numberOfDuplicates FROM t_fairly_large_table GROUP BY chr,cfrom,cto ) x NATURAL JOIN t_fairly_large_table y WHERE numberOfDuplicates > 1 The idea of this (untested) query is to produce somet

Re: [SQL] identifying duplicates in table with redundancies

Howdy, Tarlika. First, did you past correctly your query into your mail? I am asking this because your query doesn't seem work for me, it returns an empty list :-| Your most nested query, this one, -- distinct trainer id-name select distinct on (trainer_id,trainer_name)

Re: [SQL] identifying duplicates in table with redundancies

answer. There are many people on this list that can help you better Best, Oliveiros - Original Message - From: "Tarlika Elisabeth Schmitz" To: Sent: Monday, September 27, 2010 5:54 PM Subject: Re: [SQL] identifying duplicates in table with redundancies On Fri, 24 Sep 2

Re: [SQL] identifying duplicates in table with redundancies

Hallo Andreas, I reduced the problem to the innermost query: 1) SELECT DISTINCT trainer_id, trainer_name FROM student This results in a sequential table scan. Execution time 7500ms. 2) I created an INDEX ON (trainer_id, trainer_name). Then I had an index scan instead, which still cost 7000ms.

Re: [SQL] obtaining difference between minimum value and next in size

Hi, John. I am not familiar with the functions Tom's indicated and I'm sure they constitute a much more straightfoward to solve your problem. Meanwhile, if you'd like to solve it with just SQL give this a try and see if it gives you the result you want Best, Oliveiros SELECT

  1   2   >