[SQL] sql basic question

Hi All, I have this table: LABEL ID Distance SIDE 15; 119006; 0.10975569030617;1 14; 64054;0.118448307450912; 0 16; 64055;0.176240407317772; 0 20; 64057;0.39363711745035;0 19; 64056;

Re: [SQL] sql basic question

Do the child Select min, max from... Group by side Then you can do whatever is required... Thanks, Anton On Dec 28, 2012, at 12:23, Antonio Parrotta antonioparro...@gmail.com wrote: Hi All, I have this table: LABEL ID Distance SIDE 15; 119006;

Re: [SQL] sql basic question

Hi Anton, I need column LABEL and ID as well. By grouping on SIDE these column cannot be included in the query. Thanks - Antonio On 28 December 2012 12:38, Anton Gavazuk antongava...@gmail.com wrote: Do the child Select min, max from... Group by side Then you can do whatever is

Re: [SQL] sql basic question

Antonio, but then you can do join between minmax select and source table by distance and get required columns... Thanks, Anton On Dec 28, 2012, at 12:43, Antonio Parrotta antonioparro...@gmail.com wrote: Hi Anton, I need column LABEL and ID as well. By grouping on SIDE these column cannot

Re: [SQL] sql basic question

Hi Andreas, Anton, I did some test and both queries didn't worked. Maybe I was not clear with the example provided. My table contains more than 160K records with SIDE 0, 1, -1, 2, -2, 3 and -3. Example provided is a very small subset. *Andrea's *query is failing because it is getting only

Fwd: Re: [SQL] sql basic question

sorry, only a private replay and not to the list -- Ursprüngliche Nachricht -- Von: Andreas Kretschmer andr...@a-kretschmer.de An: Antonio Parrotta antonioparro...@gmail.com Datum: 28. Dezember 2012 um 15:19 Betreff: Re: [SQL] sql basic question Hi, your question was: What I want

Re: [SQL] sql basic question

Hello Andreas, apologize for the misunderstanding. Hope to clarify now. For each ID I want a min and max for each SIDE. I have about 160K records like this: label | id | distance | side ---++---+-- 15 | 119006 | 0.10975569030617 |

Re: [SQL] sql basic question

Maybe something like: test=*# select * from foo; label | id | distance | side ---++--+-- 15 | 119006 | 0.10975569030617 |1 14 | 119006 | 0.11844830745091 |0 16 | 119006 | 0.17624040731777 |0 20 | 119006 | 0.39363711745035 |

[SQL] Indexing question

In SQL, given a table T, with two fields f1, f2, is it possible to create an index such that the same record is indexed in the index, once with field f1 and once with field f2. (I am not looking for a compound index in which the key would look like f1, f2, instead there should be two entries

Re: [SQL] Query question

On 01/26/2012 04:00 AM, John Tuliao wrote: I seem to have a problem with a specific query: The inside query seems to work on it's own: select prefix from john_prefix where strpos(jpt_test.number,john_prefix.prefix) = '1' order by char_length(john_prefix.prefix) desc limit 1 but when I execute

[SQL] Query question

I seem to have a problem with a specific query: The inside query seems to work on it's own: select prefix from john_prefix where strpos(jpt_test.number,john_prefix.prefix) = '1' order by char_length(john_prefix.prefix) desc limit 1 but when I

[SQL] newbie question * compare integer in a where IN statement

Title: Firma Correo Hi everybody, I'm trying to compare in a sentence like this (using PostGres 8.3) : select * from myTable where id_integer IN ('1,2,3,4') I want to get the records which key "id_integer" is 1 or 2 or 3 or 4. the type od my

Re: [SQL] newbie question * compare integer in a where IN statement

Jose Ig Mendez, 13.07.2011 09:36: Hi everybody, I'm trying to compare in a sentence like this (using PostGres 8.3) : select * from myTable where id_integer IN ('1,2,3,4') I want to get the records which key id_integer is 1 or 2 or 3 or 4. the type od my id, of course, is integer. I've

Re: [SQL] newbie question * compare integer in a where IN statement

Hello you can try SELECT .. FROM .. WHERE id = ANY(string_to_array('1,2,3,4,5',',')) other forms are slow Regards Pavel Stehule 2011/7/13 Jose Ig Mendez jmen...@andago.com Hi everybody, I'm trying to compare in a sentence like this (using PostGres 8.3) : select * from myTable where

Re: [SQL] newbie question * compare integer in a where IN statement

Title: Firma Correo Hi, Thank you very much Pavel, that solves my problem Regards On 13/07/11 09:50, Pavel Stehule wrote: Hello you can try SELECT .. FROM .. WHERE id = ANY(string_to_array('1,2,3,4,5',',')) other

[SQL] newbie question * compare integer in a where IN statement

Title: Firma Correo Hi everybody, I'm trying to compare in a sentence like this (using PostGres 8.3) : select * from myTable where id_integer IN ('1,2,3,4') I want to get the records which key "id_integer" is 1 or 2 or 3 or 4. the type od my "id", of

Re: [SQL] concatenate question

On 2010-12-08, Tony Capobianco tcapobia...@prospectiv.com wrote: Thanks so much to everyone for your responses. You've been very helpful. I'm running PostGres 8.4 and we're migrating our datawarehouse from Oracle 10.2. I guess datatype is implicitly cast in oracle under these circumstances:

[SQL] concatenate question

Here's my table: plsql_dw=# \d tmpsv_parent_master Table staging.tmpsv_parent_master Column |Type | Modifiers +-+--- memberid | numeric | addeddate | timestamp

Re: [SQL] concatenate question

Tony Capobianco tcapobia...@prospectiv.com wrote: Here's my table: plsql_dw=# \d tmpsv_parent_master Table staging.tmpsv_parent_master Why won't this work? plsql_dw=# select memberid || addeddate from tmpsv_parent_master limit 10; ERROR: operator does not exist: numeric ||

Re: [SQL] concatenate question

Thanks so much to everyone for your responses. You've been very helpful. I'm running PostGres 8.4 and we're migrating our datawarehouse from Oracle 10.2. I guess datatype is implicitly cast in oracle under these circumstances: SQL create table tony_test as select memberid||addeddate data from

[SQL] concatenate question

Here's my table: plsql_dw=# \d tmpsv_parent_master Table staging.tmpsv_parent_master Column |Type | Modifiers +-+--- memberid | numeric

Re: [SQL] concatenate question

Ok, that worked. Why did I need to cast both as text though? plsql_dw=# select memberid::text||addeddate::text from tmpsv_parent_master limit 5; ?column? -- 4005941032010-11-16 19:32:17 4005941952010-11-16 19:33:29 4005942842010-11-16 19:34:32

Re: [SQL] concatenate question

I think the HINT is what you need to look at. Cast both columns to text. On Tue, Dec 7, 2010 at 4:37 PM, Tony Capobianco tcapobia...@prospectiv.comwrote: Here's my table: plsql_dw=# \d tmpsv_parent_master Table staging.tmpsv_parent_master Column |

Re: [SQL] concatenate question

Hey Tony, Because there is no implicit conversion to text. 2010/12/8 Tony Capobianco tcapobia...@prospectiv.com Ok, that worked. Why did I need to cast both as text though? plsql_dw=# select memberid::text||addeddate::text from tmpsv_parent_master limit 5; ?column?

Re: [SQL] concatenate question

On Tue, Dec 7, 2010 at 1:47 PM, Tony Capobianco tcapobia...@prospectiv.com wrote: Why did I need to cast both as text though? http://www.postgresql.org/docs/8.3/interactive/release-8-3.html#AEN87134 -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list

Re: [SQL] concatenate question

I don't know what Postgres version you're using but check out the doc related to String Functions and Operators. Cheers, Peter On Tue, Dec 7, 2010 at 4:47 PM, Tony Capobianco tcapobia...@prospectiv.comwrote: Ok, that worked. Why did I need to cast both as text though? plsql_dw=# select

Re: [SQL] Union Question

On Fri, Dec 3, 2010 at 8:58 AM, Plugge, Joe R. jrplu...@west.com wrote: Hello, I need to union three PostgreSQL tables and this won’t be a problem but the tables are on different servers.  Basically, I have an administrative server that needs the tables viewable in a web administrator and

[SQL] Union Question

Hello, I need to union three PostgreSQL tables and this won't be a problem but the tables are on different servers. Basically, I have an administrative server that needs the tables viewable in a web administrator and three query servers that log the needed data locally. Is there a way I can

Re: [SQL] Union Question

You may want to try dblink. http://www.postgresql.org/docs/current/static/dblink.html From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Shaun McCloud Sent: Friday, December 03, 2010 10:51 AM To: pgsql-sql@postgresql.org Subject: [SQL] Union Question

Re: [SQL] Union Question

On Dec 3, 2010, at 9:04 AM, Shaun McCloud wrote: I’m trying that, but I am getting an error that says “ERROR: function dblink_connect(unknown, unknown) does not exist” dblink is a contrib module, and needs to be installed before use:

Re: [SQL] Union Question

Technician (MCDST) Do or do not, there is no try. -Yoda -Original Message- From: Christophe Pettus [mailto:x...@thebuild.com] Sent: Friday, December 03, 2010 11:08 To: Shaun McCloud Cc: Plugge, Joe R.; pgsql-sql@postgresql.org Subject: Re: [SQL] Union Question On Dec 3, 2010

Re: [SQL] Union Question

On Dec 3, 2010, at 9:08 AM, Shaun McCloud wrote: That would be nice to see in the documentation for dblink It's true of all contrib modules; that's mentioned at the start of the contrib section: http://www.postgresql.org/docs/9.0/interactive/contrib.html -- -- Christophe Pettus

Re: [SQL] Union Question

(MCDST) Do or do not, there is no try. -Yoda -Original Message- From: Christophe Pettus [mailto:x...@thebuild.com] Sent: Friday, December 03, 2010 11:27 To: Shaun McCloud Cc: Plugge, Joe R.; pgsql-sql@postgresql.org Subject: Re: [SQL] Union Question On Dec 3, 2010, at 9:08 AM

Re: [SQL] Union Question

On 2010-12-03, Shaun McCloud smccl...@geo-comm.com wrote: --_000_7742DD496427B743BC8B7BBF6D380BA0A2F114EXCHANGE10geocomm_ Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hello, I need to union three PostgreSQL tables and this won't be a problem but

Re: [SQL] aggregation question

On Tue, Nov 30, 2010 at 7:47 PM, Samuel Gendler sgend...@ideasculptor.comwrote: I have a fact table with a number of foreign keys to dimension data and some measure columns. Let's assume I've got dimension columns a,b,c,d,e, and f and measure columns x, and y. I need to be able to find the

[SQL] aggregation question

I have a fact table with a number of foreign keys to dimension data and some measure columns. Let's assume I've got dimension columns a,b,c,d,e, and f and measure columns x, and y. I need to be able to find the value of f, x, and y for the row with min(x/y) when e in (1,2) and max(x/y) when e

[SQL] performance question

Hi, All Suppose you have a query like this SELECT * FROM t_sitesresumebydate a JOIN t_triple_association c ON IDSiteResume = IDResume WHERE dtDate BETWEEN '2009-2-1' AND '2009-2-3' AND IDHitsAccount = 378284 With this time interval it completes in less than a second. If I add one day to

Re: [SQL] performance question

Oliveiros Cristina oliveiros.crist...@marktest.pt writes: Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below). Yup, and you seem to be near the crossover point where it thinks they have equal cost.

[SQL] subquery question

Hi, I have a table: (date timestamp, id integer, value integer) What Iam trying to do is to get a result that looks like this: day sum_oddsum_even 2009-01-01 656578867 2009-01-02 876785 87667 basically a need to combine these two queries into one: SELECT

Re: [SQL] subquery question

Does this help Here is my test table data. ID;DATE;VALUE 1;2009-03-13;5 2;2009-03-13;2 3;2009-03-11;1 4;2009-03-11;2 5;2009-03-11;3 SELECT mydate AS day, SUM(CASE WHEN id % 2 = 1 THEN value END) AS sum_odd, SUM(CASE WHEN id % 2 = 0 THEN value END) AS sum_even FROM xyz GROUP BY

Re: [SQL] Copy question

Judith Altamirano a écrit : Hello every body I'm trying to extract a query as follows: copy (select * from clientes where id_cliente = 7895) to '/home/paso/sel.copy'; But it returns the next error: ERROR: error de sintaxis en o cerca de «(» at character 6 LINE 1: copy (select * from

Re: [SQL] Join question

I thought of that, but it does violate table constraints. Edward W. Rouse From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 2:00 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question I don't understand your count(total

Re: [SQL] Join question

I have tried left, right outer and inner. Edward W. Rouse From: Daniel Hernandez [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 12:30 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego

Re: [SQL] Join question

] [mailto:[EMAIL PROTECTED] On Behalf Of Lennin Caro Sent: Tuesday, August 19, 2008 11:59 AM To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse Subject: Re: [SQL] Join question --- On Tue, 8/19/08, Edward W. Rouse [EMAIL PROTECTED] wrote: From: Edward W. Rouse [EMAIL PROTECTED

[SQL] LIMIT question

I need to use a LIMIT count in a query but I also need to know how many rows the query itself would yield without the limit. I can do this inside a transaction like this BEGIN SELECT COUNT(*) from table1 where blah; select * from table1 where blah LIMIT 1000; COMMIT Now I can give some feedback

Re: [SQL] LIMIT question

On Tue, Aug 19, 2008 at 1:58 PM, EXT-Rothermel, Peter M [EMAIL PROTECTED] wrote: Now I can give some feedback like your search matches 200,000 but was limited to 1000 items. Is there a better way to accomplish this goal? Here is a nice discussion on the alternatives open to you:

Re: [SQL] Join question

PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent: Tuesday, August 19, 2008 2:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I was trying to do something like this, but couldn't get it to work. I am trying to follow the example you provided, but don't

Re: [SQL] LIMIT question

Richard Broersma [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: Is there a better way to accomplish this goal? Here is a nice discussion on the alternatives open to you: http://www.commandprompt.com/blogs/joshua_drake/2007/08/how_many_rows_do_i_have_anyway/ That doesn't really address

Re: [SQL] Join question

have you tried a right Join?Daniel Hernndez.San Diego, CA.The more you learn, the more you earn.Fax: (808) 442-0427-Original Message-From: Edward W. Rouse [EMAIL PROTECTED]: 08/15/2008 09:48 AMTo: [EMAIL PROTECTED]: Re: [SQL] Join question I have 2 tables, both have a user

Re: [SQL] Join question

PROTECTED] Sent: Monday, August 18, 2008 5:30 PM Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. The more you learn, the more you earn. Fax: (808) 442-0427 -Original Message- From: Edward W. Rouse [EMAIL PROTECTED] Date: 08

[SQL] Join question

I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other

Re: [SQL] Join question

: Friday, August 15, 2008 12:48 PM To: pgsql-sql@postgresql.org Subject: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem

Re: [SQL] Join question

On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse [EMAIL PROTECTED] wrote: The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: instead of left join try FULL OUTER

Re: [SQL] Join question

Broersma Sent: Friday, August 15, 2008 1:10 PM To: Edward W. Rouse Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse [EMAIL PROTECTED] wrote: The problem is I also have to include items from table b with that have a null user

Re: [SQL] Join question

At 12:20 PM 8/15/2008, [EMAIL PROTECTED] wrote: Date: Fri, 15 Aug 2008 13:46:14 -0400 From: Edward W. Rouse [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Subject: Re: Join question Message-ID: [EMAIL PROTECTED] I did try that, but I can't get both the values from table a with no entries in

[SQL] Query question

Hi, I can use some help with the following query please. Given a couple of tables I want to do a JOIN like operation. Except that one of the columns might be null. create table T1 ( id serial, name varchar(20) ); create table T2 ( id serial, name varchar(20) ); create table T1_T2 ( id serial,

Re: [SQL] Query question

On Thu, 22 May 2008, Medi Montaseri wrote: Hi, I can use some help with the following query please. Given a couple of tables I want to do a JOIN like operation. Except that one of the columns might be null. create table T1 ( id serial, name varchar(20) ); create table T2 ( id serial, name

Re: [SQL] Query question

Thanks Stephan, My real DDL include a forign key reference to T2.id and since I am ok with NULL value then the left outer join indeed have solved the problem. Thanks again Medi On Thu, May 22, 2008 at 2:50 PM, Stephan Szabo [EMAIL PROTECTED] wrote: On Thu, 22 May 2008, Medi Montaseri wrote:

Re: [SQL] SQL question....

Steve Midgley wrote: At 12:20 PM 5/21/2008, [EMAIL PROTECTED] wrote: Date: Wed, 21 May 2008 06:39:11 -0500 From: Karl Denninger [EMAIL PROTECTED] To: Gurjeet Singh [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: Re: SQL question Message-ID: [EMAIL PROTECTED] Also, if you don't

[SQL] SQL question....

assuming the following schema: create table access (name text, address ip) I want to construct a SELECT statement which will return ONLY tuples containing IP and name pairs IF there is an IP that has two or more NAMEs associated with it. I've not figured out how to do this; I can get a

Re: [SQL] SQL question....

create table access (name text, address ip) I want to construct a SELECT statement which will return ONLY tuples containing IP and name pairs IF there is an IP that has two or more NAMEs associated with it. many ways: select a1.* from access a1 where exists( select 1 from access

Re: [SQL] SQL question....

chester c young wrote: create table access (name text, address ip) I want to construct a SELECT statement which will return ONLY tuples containing IP and name pairs IF there is an IP that has two or more NAMEs associated with it. many ways: select a1.* from access a1 where exists(

Re: [SQL] SQL question....

I think this is what you're looking for: SELECT * FROM access WHERE ip IN(SELECT ip FROM access GROUP BY ip HAVING count(*) 1) On Tue, May 20, 2008 at 3:17 PM, Karl Denninger [EMAIL PROTECTED] wrote: chester c young wrote: create table access (name text, address ip) I want to

Re: [SQL] SQL question....

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger [EMAIL PROTECTED] wrote: assuming the following schema: create table access (name text, address ip) I want to construct a SELECT statement which will return ONLY tuples containing IP and name pairs IF there is an IP that has two or more

Re: [SQL] SQL question....

Gurjeet Singh wrote: On Tue, May 20, 2008 at 11:44 PM, Karl Denninger [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: assuming the following schema: create table access (name text, address ip) I want to construct a SELECT statement which will return ONLY tuples

Re: [SQL] SQL question....

Gurjeet Singh wrote: On Wed, May 21, 2008 at 4:47 AM, Karl Denninger [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Gurjeet Singh wrote: On Tue, May 20, 2008 at 11:44 PM, Karl Denninger [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]

[SQL] Syntax question: use of join/using with fully qualified table name

I've got a join where a certain column name appears twice: select username,last_name from eg_member join eg_membership using (member_id) join eg_account using (account_id) join eg_person using (person_id); ERROR: common column name person_id appears more than once in left table My first

Re: [SQL] Syntax question: use of join/using with fully qualified table name

Bryce Nesbitt escribió: I've got a join where a certain column name appears twice: select username,last_name from eg_member join eg_membership using (member_id) join eg_account using (account_id) join eg_person using (person_id); ERROR: common column name person_id appears more than once in

Re: [SQL] Syntax question: use of join/using with fully qualified table name

Bryce Nesbitt [EMAIL PROTECTED] writes: I've got a join where a certain column name appears twice: select username,last_name from eg_member join eg_membership using (member_id) join eg_account using (account_id) join eg_person using (person_id); ERROR: common column name person_id appears

Re: [SQL] SQL question: Highest column value of unique column pairs

by date desc,name regards, Marc Mamin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Jenkins Sent: Saturday, January 12, 2008 1:10 AM To: pgsql-sql@postgresql.org Subject: [SQL] SQL question: Highest column value of unique column pairs Hi, I have

Re: [SQL] SQL question: Highest column value of unique column pairs

Kevin Jenkins wrote: Thanks! How would I find the highest score in the union of the two tables? I tried this but it can't find unionTable: SELECT * FROM (select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from

[SQL] SQL question: Highest column value of unique column pairs

Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John,

Re: [SQL] SQL question: Highest column value of unique column pairs

Kevin Jenkins wrote: Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien

Re: [SQL] SQL question: Highest column value of unique column pairs

Thanks! How would I find the highest score in the union of the two tables? I tried this but it can't find unionTable: SELECT * FROM (select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable) as

Re: [SQL] sql query question ?

Dear Shane, Thanks for the reply and your observation about the word i have used. It is idle odometer reading. The actual Scenario is that the vehicle is taken by the driver. When he comes the next day. He is suppose to login again. Here i am trying to find out how much distance has the

[SQL] sql query question ?

Hi All, I have a table called vehicle_duty_cycle_summary vehicle_master_id | starting_odometer | ending_odometer | login_time | logout_time ---+---+-++ 4 |

Re: [SQL] sql query question ?

Trilok Kumar wrote: Hi All, I have a table called vehicle_duty_cycle_summary vehicle_master_id | starting_odometer | ending_odometer | login_time | logout_time

[SQL] Aggregate question (Sum)

Hi All, I want to know if there are an easy manner to do an SQL like this bellow where TotalOrdersValue sum the order.total just one time per order (as count(DISTINCT order.id) do) SELECT order.dtorder , Count( DISTINCT order.fk_customer ) AS QtyCustomer , Count( DISTINCT order.id )

Re: [SQL] Aggregate question (Sum)

Hi Rodrigo, thanks for reply Rodrigo De León wrote: On Nov 19, 2007 3:46 PM, Luiz K. Matsumura [EMAIL PROTECTED] wrote: If someone can give me some hint I will apreciate. This is more of a normalization problem. See:

[SQL] ALL() question

Hello, I have a problem with the ALL() subquery expression. I have three tables: - specimens - test_bits - specimen_test_bits The specimen_test_bits table contains two foreign keys, one to specimens(id), another to test_bits(id). Here is an output of specimen_test_bits: muridae= select * from

Re: [SQL] ALL() question

Julien Cigar wrote: What I would like is a query that returns all the specimen_id of this table which have _all_ the given test_bit_id. [snip] With the following I got a syntax error: select specimen_id from specimen_test_bits where test_bit_id = all(1,2,3,4); It's expecting an array

Re: [SQL] ALL() question

On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote: Julien Cigar wrote: What I would like is a query that returns all the specimen_id of this table which have _all_ the given test_bit_id. [snip] With the following I got a syntax error: select specimen_id from

Re: [SQL] ALL() question

I finally found a solution: SELECT specimen_id FROM specimen_test_bits GROUP BY specimen_id HAVING array_accum(test_bit_id) = '{2,3,4}'; .. but I don't think it's very clean .. what do you think ? Thanks On Wed, 2007-11-14 at 15:50 +0100, Julien Cigar wrote: On Wed, 2007-11-14 at 11:56

Re: [SQL] ALL() question

The doc says The right-hand side is a parenthesized subquery, which must return exactly one column... That's what you have if using ... where test_bit_id = all(select id from test_bits where id in (1,2,3,4)); The doc continues ...The left-hand expression is evaluated and compared to each row of

Re: [SQL] ALL() question

On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote: With the following I got a syntax error: select specimen_id from specimen_test_bits where test_bit_id = all(1,2,3,4); where test_bit_id in (1,2,3,4) group by specimen_id having count(distinct test_bit_id) = 4; depesz --

Re: [SQL] ALL() question

Julien Cigar wrote: I finally found a solution: SELECT specimen_id FROM specimen_test_bits GROUP BY specimen_id HAVING array_accum(test_bit_id) = '{2,3,4}'; .. but I don't think it's very clean .. The key question is whether you can rely on getting (2,3,4) or whether you might get

Re: [SQL] ALL() question

Julien Cigar wrote: On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote: Julien Cigar wrote: What I would like is a query that returns all the specimen_id of this table which have _all_ the given test_bit_id. [snip] With the following I got a syntax error: select specimen_id from

Re: [SQL] ALL() question

Thanks :) it works as expected Julien On Wed, 2007-11-14 at 14:31 +0100, hubert depesz lubaczewski wrote: On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote: With the following I got a syntax error: select specimen_id from specimen_test_bits where test_bit_id =

Re: [SQL] ALL() question

Julien Cigar escreveu: Hello, I have a problem with the ALL() subquery expression. I have three tables: - specimens - test_bits - specimen_test_bits The specimen_test_bits table contains two foreign keys, one to specimens(id), another to test_bits(id). Here is an output of

[SQL] general question on optimizer

I have found that in many complex queries left join is exponentially faster than a (not) exists clause. I don't understand why, generally speaking, this is so frequently so effective. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam

Re: [SQL] Quick question re foreign keys.

D'Arcy J.M. Cain skrev: On Wed, 24 Oct 2007 11:00:47 +0800 Paul Lambert [EMAIL PROTECTED] wrote: It's marked not null as a result of being part of the primary key for that table which I can't really get around. I can get away with not having the foreign key though, so I'll have to go down

Re: [SQL] Quick question re foreign keys.

Forgive my butting in, but frankly, most of the times, whenever I find myself in a very 'exceptional problem' such as this one, I always end up questioning the basic design due to which I am stuck in the first place. Paul, it seems that probably there is a basic design issue here. All the best

Re: [SQL] Quick question re foreign keys.

On Wed, 24 Oct 2007 09:43:10 +0200 Nis Jørgensen [EMAIL PROTECTED] wrote: Well, I have a couple of times had the need to have a primary key/uniqueness constraint with one column nullable (indicating Not Applicable). The problem is that we have only one NULL, which for comparison purposes is

[SQL] Quick question re foreign keys.

I have a table where one column references by foreign key a column from another table. However, the column in this first table does not always contain data which results in a not-null constraint violation when I attempt an insert. My question therefore is, is it possible to create a foreign

Re: [SQL] Quick question re foreign keys.

My question therefore is, is it possible to create a foreign key that is conditional, i.e. only enforce the foreign key where the value in that table is not null. My understanding from reading previous threads on this topic is the answer is no, however you could make your own pseudo-foreign

Re: [SQL] Quick question re foreign keys.

Paul Lambert [EMAIL PROTECTED] writes: I have a table where one column references by foreign key a column from another table. However, the column in this first table does not always contain data which results in a not-null constraint violation when I attempt an insert. My question

Re: [SQL] Quick question re foreign keys.

Tom Lane wrote: If I'm understanding you correctly, the problem is not the foreign key, it's that you marked the column NOT NULL. A foreign key constraint by itself will allow a NULL in the referencing column to pass. You choose whether you want to allow that or not by separately applying a

Re: [SQL] Quick question re foreign keys.

On Wed, 24 Oct 2007 11:00:47 +0800 Paul Lambert [EMAIL PROTECTED] wrote: It's marked not null as a result of being part of the primary key for that table which I can't really get around. I can get away with not having the foreign key though, so I'll have to go down that path. It can't be

Re: [SQL] SQL query question

On Sep 21, 12:09 am, [EMAIL PROTECTED] wrote: Write the query (or queries if necessary) needed to count the number of employees in each employee's department who are paid more than their manager. SELECT e.dept, COALESCE (SUM (1), 0) AS n FROM employees e JOIN employees m ON

[SQL] pg_dump question

Hello every body!! I have a quesyion respect pg_dump... I need to backup the db structure, I suposse that I do with pg_dump -s, but I want to restore but just with some tables with the db catalogues, can I do this?, I mean, restore al the db structure without data and after

  1   2   3   4   >