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 |

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 |

Fwd: Re: [SQL] sql basic question

sorry, only a private replay and not to the list -- Ursprüngliche Nachricht -- Von: Andreas Kretschmer An: Antonio Parrotta Datum: 28. Dezember 2012 um 15:19 Betreff: Re: [SQL] sql basic question Hi, your question was: "What I want to achieve is a result table with min an

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 distinc

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 wrote: Hi Anton, I need column LABEL and ID as well. By grouping on SIDE these column cannot be included in the query.

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 wrote: > Do the child Select min, max from... Group by side > > Then you can do whatever is required... > > Thanks, > Ant

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 wrote: Hi All, I have this table: LABEL ID Distance SIDE "15"; 119006; 0.10975569030617;1

Re: [SQL] SQL View to PostgreSQL View

From: Rehan Saleem [mailto:pk_re...@yahoo.com] Sent: Sunday, February 26, 2012 1:50 PM To: pgsql-sql@postgresql.org Subject: SQL View to PostgreSQL View Hi , I am trying to convert sql view to postgresql view but i am getting the following error i dont know how to handle dbo. in postgresql and

Re: [SQL] SQL View to PostgreSQL View

Hi Rehan, Whilst I'm not sure what you exactly mean with ', while this is working perfectly fine in sql,..', it's kind of odd that you get result when you execute the sql. I'd suggest looking at your search_path (show search_path), which normally resolves to 'user', 'public'; If you do h

Re: [SQL] SQL View to PostgreSQL View

On Sunday, February 26, 2012 10:50:16 am Rehan Saleem wrote: > Hi , > I am trying to convert sql > view to postgresql view but i am getting the > following error i dont know how > to handle dbo. > in postgresql and when i remove dbo. from table name then view got created > but it does not show any

Re: [SQL] sql query problem

On Jan 17, 2012, at 8:35, Andreas Kretschmer wrote: > Alok Thakur wrote: > >> Dear, >> >> I am trying to provide you as much details as possible. >> >> answer` ( >> `id` int(10) NOT NULL AUTO_INCREMENT, >> `question_id` int(10) NOT NULL, >> `user_id` int(10) NOT NULL, >> `answer` int(10)

Re: [SQL] sql query problem

Alok Thakur wrote: > Dear, > > I am trying to provide you as much details as possible. > > answer` ( > `id` int(10) NOT NULL AUTO_INCREMENT, > `question_id` int(10) NOT NULL, > `user_id` int(10) NOT NULL, > `answer` int(10) NOT NULL, -> > `status` tinyint(1) NOT NULL, --> Status wil

Re: [SQL] sql query problem

Dear, I am trying to provide you as much details as possible. answer` ( `id` int(10) NOT NULL AUTO_INCREMENT, `question_id` int(10) NOT NULL, `user_id` int(10) NOT NULL, `answer` int(10) NOT NULL, -> `status` tinyint(1) NOT NULL, --> Status will be 0 or 1 means wrong or right answer

Re: [SQL] sql query problem

It seems question is not clear... I could not determine what should be in column Attended, and based on what should define passed/failed But quick tip would be SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM UserTable INNER JOIN result ON UserTable.id = result.user_id Sent

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_id, status (0 or 1) >

Re: [SQL] SQL gotcha

Στις Tuesday 15 February 2011 01:29:25 ο/η Dean Gibson (DB Administrator) έγραψε: > As you all know, when you specify an alias for a table or column name, > the keyword "AS" is usually optional. I personally like to always > include the "AS" keyword, if for no other reason than readability, and

Re: [SQL] sql disaster - subquery error but delete continues

Greg Caulton, 10.09.2010 11:46: delete from form_record_details where form_record_id in (select form_record_id from forms where form_id= 40003656) Seems fine at 1am. However the subquery has a typo in it - there is no form_record_id in the forms table But rather than psql throwing an error..

Re: [SQL] SQL Developer accessing PostgreSQL

Snyder, James, 29.03.2010 18:33: Hello, Is there a way to configure Oracle’s SQL Developer to access a PostgreSQL database? Thanks,Jim As others have pointed out, it's not possible. The Postgres Wiki contains a list of GUI Tools that work with Postgres: http://wiki.postgresql.org/wiki/Commu

Re: [SQL] SQL Developer accessing PostgreSQL

Squirrel been tested. It is opensource, and is done in Java using JDBC to support many databases. It is a universal SQL client, see http://www.squirrelsql.org/ El 29/03/10 18:22, Hiltibidal, Rob escribió: SQL Developer accessing PostgreSQL I recommend Aqua Data Studio  

Re: [SQL] SQL Developer accessing PostgreSQL

I recommend Aqua Data Studio Just drop in the jdbc jar From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Snyder, James Sent: Monday, March 29, 2010 11:34 AM To: pgsql-sql@postgresql.org Subject: [SQL] SQL Developer accessing PostgreSQL Hello

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

Snyder, James, 29.03.2010 18:25: Thanks for all the dialog on this subject. My "version" was derived from the postgreSQL's .jar file (specifically named "postgresql-8.4-701.jdbc4.jar") that I'm using. When I do the following: select version() I get the following: PostgreSQL 8.3.6 Then you

Re: [SQL] SQL Developer accessing PostgreSQL

On 03/29/10 20:33, Snyder, James wrote: Hello, Is there a way to configure Oracle’s SQL Developer to access a PostgreSQL database? IMHO, no. And (even if it possible) it is completely useless, since the Postgresql has the "psql" program far better than any oracle tool and than all oracle too

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

ng to check this out. Thanks...Jim -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Thomas Kellerer Sent: Friday, March 26, 2010 3:15 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] SQL syntax rowcount value as an extra colu

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

Jayadevan M, 26.03.2010 07:56: Thank you for setting that right. Apologies for not checking version. The orginal poster stated that he is using 8.4, so that solution will work for him. Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

Hi, >It works, but you should use a recent version: >test=*# select count(1) over (), i from foo; > count | i >---+ > 8 | 1 > 8 | 2 > 8 | 3 > 8 | 6 > 8 | 7 > 8 | 9 > 8 | 13 > 8 | 14 >(8 rows) > test=*# select version(); >

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

In response to Jayadevan M : > Hi, > I don't think so. > Oracle - > SQL> select count(*) over () as ROWCOUNT , first_name from people; > > ROWCOUNT FIRST_NAME > -- > - > --- > 6 Mary

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

Regards, Jayadevan From: Thomas Kellerer To: pgsql-sql@postgresql.org Date: 26/03/2010 03:26 Subject: Re: [SQL] SQL syntax rowcount value as an extra column in the result set Sent by:pgsql-sql-ow...@postgresql.org Snyder, James wrote on 25.03.2010 22:33: > I’m using Postg

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

Hi, Is this what you are trying to do? postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) I do not know about the performance impact of s

Re: [SQL] SQL syntax rowcount value as an extra column in the result set

Snyder, James wrote on 25.03.2010 22:33: I’m using PostgreSQL (8.4.701) There is no such version. The current version is 8.4.3 On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people The same syntax will work on

Re: [SQL] SQL state 58P01 triggered by a database script execution

In response to aymen marouani : > Thanks for the help, > > In deed I found those lines in my script > > "-- > -- TOC entry 25 (class 1255 OID 16409) > -- Dependencies: 6 > -- Name: bt_metap(text); Type: FUNCTION; Schema: public; Owner: postgres > -- > > CREATE FUNCTION bt_metap(relname text, OUT

Re: [SQL] SQL state 58P01 triggered by a database script execution

Thanks for the help, In deed I found those lines in my script "-- -- TOC entry 25 (class 1255 OID 16409) -- Dependencies: 6 -- Name: bt_metap(text); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION bt_metap(relname text, OUT magic integer, OUT version integer, OUT root integer,

Re: [SQL] SQL state 58P01 triggered by a database script execution

In response to aymen marouani : > Hi, > > I have a database under Postgres v8.3 and I generated its backups script using > PGAdmin III. > I executed the same script under Postgres v8.2 in order to create the same > database but I get the following error > > " ERROR: could not access file "$libdir

Re: [SQL] SQL moving window averages/statistics

Just 3 points ... 1) don't use "date" as a column name because it's a data type. 2) to_char(current_date, 'MM')||to_char(current_date, 'DD') is equivalent to to_char(current_date, 'MMDD') 3) you should get the same result with ... where icao='KSFO' and (EXTRACT (MONTH from d

Re: [SQL] SQL Subqueries on each result row

On Wed, Sep 23, 2009 at 6:33 PM, Jim wrote: > On Sep 23, 5:43 am, AnthonyV wrote: > > Hello, > > > > I have a table like : > > > >date|value > > --- > > 2009-09-19 | 1 > > 2009-09-20 | 2 > > 2009-09-21 | 6 > > 2009-09-22 | 9 > > 200

Re: [SQL] SQL Subqueries on each result row

On Sep 23, 5:43 am, AnthonyV wrote: > Hello, > > I have a table like : > >    date        |    value > --- > 2009-09-19 |      1 > 2009-09-20 |      2 > 2009-09-21 |      6 > 2009-09-22 |      9 > 2009-09-23 |      1 > > I'd like a request which gives me the sum of each

Re: [SQL] SQL report

Hi Rob, I have default B-Tree indexes created for each of the indexed columes and primary key columes. (No multiple columes indexe or NULL FIRST or DESC/ASC). I am using PostgreSQL 8.3 with the auto vacuum daemon on. I assume analyse will be automatically run to collect statistics for use b

Re: [SQL] SQL report

Hi Steve, Thanks for you suggestions. In my senario, what is current depends on users. Because if user wants a status report at 00:00 1st Jan 2009, then 00:00 1st Jan 2009 is current. So it is not possible to flag any records as current unless the user tells us what is current. cheers Joh

Re: [SQL] SQL report

Did you look at the query plans for the various record counts? That might show which index is missing or misinformed :). I wonder if clustering the status table on objectid would help? This does then require maintenance so you might only load it at 75%. wkipj...@gmail.com wrote: Hi Rob,

Re: [SQL] SQL report

wkipj...@gmail.com wrote: I have the following senario. I have a tracking system. The system will record the status of an object regularly, all the status records are stored in one table. And it will keep a history of maximum 1000 status record for each object it tracks. The maximum objects t

Re: [SQL] SQL report

I would be curious to know the performance curve for let's say 20K, 40K , 60K, 80K, 100K records. And what sort of indexing you have, whether or not it's clustered, re-built and so on. One could envision partitioning the status table such that recent records were grouped together (on the assu

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

On Jul 28, 2009, at 5:10 PM, nha wrote: Hello, Le 28/07/09 14:25, Daryl Richter a écrit : On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: hello list, i have some problems with an sql-statement which runs on oracle but not on postgresql (i want update only if result of SELECT is not emp

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

Hello, Le 28/07/09 14:25, Daryl Richter a écrit : > > On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: > >> hello list, >> i have some problems with an sql-statement which runs on oracle but >> not on postgresql (i want update only if result of SELECT is not >> empty, the SELECT-queries are

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: hello list, i have some problems with an sql-statement which runs on oracle but not on postgresql (i want update only if result of SELECT is not empty, the SELECT-queries are identical): UPDATE table1 t1 SET (t1.id) =

Re: [SQL] SQL File in encrypted form

On 2009-06-19, Jyoti Seth wrote: > This is a multipart message in MIME format. > > --=_NextPart_000_0001_01C9F0F8.92EE3490 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi All, > > > > I want to send the sql script file to a client but wants to

Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

Alvaro Herrera writes: > Another way to phrase the WHERE clause is with the OVERLAPS operator, > something like this: > WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 > 08:59:59') > What I'm not so sure about is how optimizable this construct is. Not at all :-( --- or a

Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

James Kitambara wrote: > Dear Srikanth, > You can solve your problem by doing this > > THE SQL IS AS FOLLOWS >   ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE > TABLE NAME time_interval > >  COUNT (*) FROM   >     (select customer_id, log_session_id, start_ts, end_ts ,

Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

From: Richard Huxton To: Srikanth Cc: pgsql-sql@postgresql.org Sent: Tuesday, 17 March, 2009 18:06:09 Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Dear all, I have a table that records User Login Sessions with two timestamp

Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

That did the job. Thanks. Am new to SQL, does not even know that there exists an Operator called OVERLAPS. Thanks Richard ../rssrik --- On Tue, 17/3/09, Richard Huxton wrote: From: Richard Huxton Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end tim

Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

Srikanth wrote: > Dear all, > > I have a table that records User Login Sessions with two timestamp fields. > Basically Start of Session and End of a Session (start_ts and end_ts). Each > row in the table identifies a session which a customer has used. > > Data from the table (session): >

Re: [SQL] SQL syntax I've been unable to figure out....

2008/12/25 Karl Denninger : > Assuming a table containing: > > name text > address text > uri text > > I wish to run a query that will return those rows where: > > ("name" is not null) AND (distinct) (uri is the same for two or more entries > AND name is different between the two entries)) > > Exam

Re: [SQL] SQL Statement Missing From Log

Bryce Nesbitt <[EMAIL PROTECTED]> writes: > I'm getting a bunch of: > 2008-10-25 14:36:59 PDT ERROR: syntax error at or near "SET" at character 9 > 2008-10-25 14:36:59 PDT ERROR: syntax error at or near "fetch" at > character 9 > 2008-10-25 14:36:59 PDT ERROR: current transaction is aborted, co

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 do

Re: [SQL] SQL question....

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 have it already, yo

Re: [SQL] SQL question....

Gurjeet Singh wrote: On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <[EMAIL PROTECTED] > wrote: Gurjeet Singh wrote: On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[EMAIL PROTECTED] > wrote: Gurjeet Singh wrote:

Re: [SQL] SQL question....

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

Re: [SQL] SQL question....

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

Re: [SQL] SQL question....

On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[EMAIL PROTECTED]> wrote: > Gurjeet Singh wrote: > >> On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[EMAIL PROTECTED]> [EMAIL PROTECTED]>> wrote: >> >> assuming the following schema: >> >>create table access (name text, address ip) >

Re: [SQL] SQL question....

Gurjeet Singh wrote: 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 cont

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 o

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

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....

> 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 ac

Re: [SQL] SQL standards in Mysql

Dean Gibson (DB Administrator) wrote: ...For example, I think phpBB is the only major message board software that supports PostgreSQL (see http://www.phpbb.com/about/features/compare.php ), and in fact has for some time. Of course, they have a DB abstraction layer (wow, what an concept!), whi

Re: [SQL] SQL standards in Mysql

On Saturday 23 February 2008 07:50, Tom Lane wrote: >Hmm ... while ... > so I'm disinclined to throw the first > stone ... Meanwhile, Throw cones, not stones. http://cfx.kymi.com/lotsacones.jpg These things/projectiles hurt not so much. And it's fun ! BR, -- Aarni Ruuhimäki

Re: [SQL] SQL standards in Mysql

Ken Johanson <[EMAIL PROTECTED]> writes: > Here's one Mysql developer's response to adding (fixing) the > integer/bigint/tinyint types to their CAST function: > http://bugs.mysql.com/bug.php?id=34562 Hmm ... while I'm certainly not someone to defend mysql on a regular basis, I can see their poin

Re: [SQL] SQL standards in Mysql

On Fri, Feb 22, 2008 at 11:03 PM, Mail Delivery Subsystem <[EMAIL PROTECTED]> wrote: > This is an automatically generated Delivery Status Notification > > Delivery to the following recipient failed permanently: > > [EMAIL PROTECTED] > To: "Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]>

Re: [SQL] SQL standards in Mysql

On Fri, Feb 22, 2008 at 7:39 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > So, I went with PostgreSQL. Why? From the book, it was clear that > MySQL lacked so many features of a decent SQL DB. In particular (at the > time) VIEWs and sub-selects. Note that unless someone's d

Re: [SQL] SQL standards in Mysql

On 2008-02-22 16:13, Scott Marlowe wrote: There's example after example of things in the mysql bug database that should make anyone considering it as a database engine cringe and walk away shaking their head. I don't understand why anyone wanting a real SQL DB would pick MySQL. Four years ago

Re: [SQL] SQL standards in Mysql

On Fri, Feb 22, 2008 at 1:57 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Ken Johanson wrote: > > Here's one Mysql developer's response to adding (fixing) the > > integer/bigint/tinyint types to their CAST function: > > > > http://bugs.mysql.com/bug.php?id=34562 > > So they are anal too, bu

Re: [SQL] SQL standards in Mysql

Ken Johanson wrote: > Here's one Mysql developer's response to adding (fixing) the > integer/bigint/tinyint types to their CAST function: > > http://bugs.mysql.com/bug.php?id=34562 So they are anal too, but in the opposite direction? -- Alvaro Herrerahttp://www.

Re: [SQL] Sql ORDER BY and ASC/DESC question

"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: > >> ORDER BY >> CASE ? >> WHEN 1 THEN name ASC > > Uh, no, putting the ASC/DESC decoration inside a CASE like that is not > gonna work doh! I had a feeling something was wrong but couldn't put my finger on it

Re: [SQL] Sql ORDER BY and ASC/DESC question

Gregory Stark <[EMAIL PROTECTED]> writes: > If you're not concerned with the planner being able to find indexes to satisfy > these orderings (ie, you don't mind always doing a sort) you could do > something like: > ORDER BY > CASE ? > WHEN 1 THEN name ASC > WHEN 2 THEN name DESC > WHEN 3 THE

Re: [SQL] Sql ORDER BY and ASC/DESC question

"A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes: >> Hello everyone, >> >> I have following problem: am using pl/sql functions to trigger some >> sql code and i need to pass ORDER_BY column name and ASC/DESC sorting >> order a

Re: [SQL] Sql ORDER BY and ASC/DESC question

Actually there might be assuming your function is a set returning function. This example eg works perfectly and sorts the output of the function without having to use execute. CREATE TABLE "public"."error_types" ( "id" SERIAL, "errdesc" TEXT NOT NULL, "autofix" BOOLEAN DEFAULT false NOT

Re: [SQL] Sql ORDER BY and ASC/DESC question

am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes: > Hello everyone, > > I have following problem: am using pl/sql functions to trigger some > sql code and i need to pass ORDER_BY column name and ASC/DESC sorting > order as an input parameters into that function and order t

Re: [SQL] SQL tree duplication

> Hi, > > I have the following kind of sql structure, in 3 levels: > > --- > create table documents ( > id serial, > name varchar(50), > primary key (id) > ); > > create table lines ( > id serial, > name varchar(50), > document_id integer, > primary key (id), > f

Re: [SQL] SQL dealing with subquery

Following up my treaty rate thoughts, if I'm trying to get the lowest treaty payee (and rate) from a specific list of payees for every possible payor country, the following seems to work, but is it right? I'm specifically wondering about the group by clauses. (Or if there is a better way.) [table t

Re: [SQL] SQL dealing with subquery

Thanks. It throws off a few extra countries where there is only one treaty, but those are few enough that I can handle them manually. I thought the solution was also going to give me insight into how to select just the lowest rate from each couple, (i.e. for each payor, who is the lowest rate

Re: [SQL] SQL dealing with subquery

On Jan 15, 2008 1:04 PM, Bryan Emrys <[EMAIL PROTECTED]> wrote: > In other words, in the sample above, I only want to return: > 'Canada','Ireland',0 > 'Canada','Netherlands',5 Try (untested): SELECT t2.* FROM (SELECT payor FROM treaty_rates WHEREpayee IN ('Netherlands'

Re: [SQL] SQL stored function inserting and returning data in a row.

> What about > $$ > INSERT INTO ; > select currval('seq_matchmaking_session_id'); > $$ language sql; > > ? Hello, I'm not sure that this would return the correct id in case of concurrent calls to your function. I'm using following kind of function to manage reference tables: HTH, Marc Ma

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

Hello Kevin, I would use "select distinct on" to first isolate the candidates in (1) and (2) and then reitere the query on this sub result: (the query below will retrieve the last score, not the best one...) something like (not tested): select distinct on (date,name) date,name,score from

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 m

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 union

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 stored function inserting and returning data in a row.

Hello > By the way, is there any performance difference between pure SQL and > PL/pgSQL stored functions? If I remember correctly there was such a > distinction between pure SQL statement and PL/PLSQL stored procedures > (Oracle), in the sense that PL/PLSQL stored procedures are executed > within

Re: [SQL] SQL stored function inserting and returning data in a row.

> What about > $$ > INSERT INTO ; > select currval('seq_matchmaking_session_id'); > $$ language sql; > > ? Indeed... :-( For some reason, I thought that it was not possible to have to SQL statement in an SQL stored function. By the way, is there any performance difference between pure SQL a

Re: [SQL] SQL stored function inserting and returning data in a row.

On Jan 11, 2008 4:23 AM, Daniel Caune <[EMAIL PROTECTED]> wrote: Please ignore my post. I havent' read your message carefully enough. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://

Re: [SQL] SQL stored function inserting and returning data in a row.

On Jan 11, 2008 4:23 AM, Daniel Caune <[EMAIL PROTECTED]> wrote: > Hi, > > Is there any way to define a SQL stored function that inserts a row in a > table and returns the serial generated? Maybe you just need INSERT ... RETURNING? http://www.postgresql.org/docs/8.2/interactive/sql-insert.html "

Re: [SQL] SQL stored function inserting and returning data in a row.

Daniel Caune wrote: Hi, Is there any way to define a SQL stored function that inserts a row in a table and returns the serial generated? CREATE TABLE matchmaking_session ( session_id bigint NOT NULL DEFAULT nextval('seq_matchmaking_session_id'), ... ); CREATE FUNCTION create_matchmaking_ses

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 vehic

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 ---+---+-++---

Re: [SQL] sql query - create replace function

Iuri Sampaio wrote: Hi all, I created a script to install postgresql. One of the steps is to run a sql query. the problem is that i need to run the query as postgres user, plus it needs to be at psql shell prompt command line, i.e: Why does it need to be at the psql prompt? I expected somet

Re: [SQL] SQL INSERT/TRIGGER Help

Poovendran Moodley escribió: > I'm not really sure how to the *currval() *method. I've read up on it and I > noticed it works with *nextval()* and *setval()*. The parameter for * > currval()* is a regex - is there a regex to represent the most recently > automatically generated number ( i.e. a seri

Re: [SQL] SQL INSERT/TRIGGER Help

Aww man thank you so much! It worked like a charm! Have a smashing day :D On Dec 10, 2007 9:43 AM, A. Kretschmer <[EMAIL PROTECTED]> wrote: > am Mon, dem 10.12.2007, um 9:27:58 +0200 mailte Poovendran Moodley > folgendes: > > I'm not really sure how to the currval() method. I've read up on it a

Re: [SQL] SQL INSERT/TRIGGER Help

am Mon, dem 10.12.2007, um 9:27:58 +0200 mailte Poovendran Moodley folgendes: > I'm not really sure how to the currval() method. I've read up on it and I > noticed it works with nextval() and setval(). The parameter for currval() is a > regex - is there a regex to represent the most recently auto

Re: [SQL] SQL INSERT/TRIGGER Help

I'm not really sure how to the *currval() *method. I've read up on it and I noticed it works with *nextval()* and *setval()*. The parameter for * currval()* is a regex - is there a regex to represent the most recently automatically generated number ( i.e. a serial field)? If there isn't, I was thin

Re: [SQL] SQL INSERT/TRIGGER Help

am Mon, dem 10.12.2007, um 8:36:44 +0200 mailte Poovendran Moodley folgendes: > So obviously I need to insert into the table Observation_Value first before I > can insert into table Observation, but how to I get the automatically > generated > foreign key? You can simple use currval() for this.

Re: [SQL] SQL state: 22P02

On Nov 22, 2007 11:24 AM, Franklin Haut <[EMAIL PROTECTED]> wrote: >num return > -- >0 0 >null false >1212 >a false >12ab false > > it´s possible get these results ? Try: SELECT NUM , CASE WHEN TRIM(NUM) ~

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 (e

Re: [SQL] SQL query to display like this

RPK wrote: select PaySlipDate,EmpID, case ADCode when 'GPF' then ADAmount else 0 end GPF, case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV' from EmpSalaryRecord It is showing like this: Date EmpID GPFGPF.ADV - 01-Jul-07 101

  1   2   3   4   5   >