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

Igor Kryltsov wrote: We have table create table group_facility ( group_id integer not null, facility_id integer not null ) It stores facilities membership in group. For example: "North Region" - facilityA, facilityB I need to extract groups from this table which contain facilityN AND facilityZ and

Re: [SQL] sql question:

Chris Aiello wrote: > hi all: > > I'm trying to figure out SQL to do the following: > I have an application that tracks SQL that is being sent to the database, > and one of it's features is the ability to identify whether a query is an > insert, update, delete, select, select with all rows return

RE: [SQL] SQL question

Deepali, Bruce Momjian's book on SQL provides a great intro to SQL, including joins. http://www.ca.postgresql.org/docs/aw_pgsql_book/ If you have a specific question, please post that to a list Good luck! -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On

Re: [SQL] SQL question

Deepali, >I have a simple question about nested SQL statements. I > remember > having learnt of another way of writing nested SQL statements, using > Joins I > guess. ... I'm afraid that your question is much to general to be answered. Frankly, I'm not sure what you mean by "

Re: [SQL] SQL question

Thomas Lockhart <[EMAIL PROTECTED]> writes: >> The immediate cause of this gripe was discussed just a day or so ago >> on one or another of the pgsql lists. The timestamp-to-date conversion >> routine has this weird idea that it should kick out an error instead >> of returning NULL when presented

Re: [SQL] SQL question

Philip Warner <[EMAIL PROTECTED]> writes: > At 02:26 17/07/00 -0400, Tom Lane wrote: >> Well before my time, I guess --- as long as I've been paying attention, >> the function manager's approach was to call the routine first and *then* >> insert a NULL result ... if the routine hadn't crashed firs

Re: [SQL] SQL question

At 02:26 17/07/00 -0400, Tom Lane wrote: > >Well before my time, I guess --- as long as I've been paying attention, >the function manager's approach was to call the routine first and *then* >insert a NULL result ... if the routine hadn't crashed first. That's >about as braindead a choice as I can

Re: [SQL] SQL question

> The immediate cause of this gripe was discussed just a day or so ago > on one or another of the pgsql lists. The timestamp-to-date conversion > routine has this weird idea that it should kick out an error instead > of returning NULL when presented with a NULL timestamp. That's a bug > IMHO, an

Re: [SQL] SQL question

Philip Warner <[EMAIL PROTECTED]> writes: > Another alternative would be to define a 'coalesce' function (I don't think > PG has one), which takes an arbitrary number of arguments and returns the > first non-null one. We surely do have that! It even works pretty well in 7.0 ;-) (I think there we

Re: [SQL] SQL question

At 13:41 17/07/00 +1000, Carolyn Lu Wong wrote: >> Try >> select count(*) from table1 where account_no = 1 and start_date_time is >> null; >> >> and see if you get 0. > >Yes, i get 0 from running the above query, but it fails if i re-arrange >the where clause to: > > select * from table

Re: [SQL] SQL question

Philip Warner wrote: > > At 12:07 17/07/00 +1000, Carolyn Lu Wong wrote: > >I have table with the following definition: > > > > create table table1( > > account_no int4, > > start_date_tme datetime > > > > ); > > > >The table may

Re: [SQL] SQL question

At 12:07 17/07/00 +1000, Carolyn Lu Wong wrote: >I have table with the following definition: > > create table table1( > account_no int4, > start_date_tme datetime > > ); > >The table may contain null values for start_date_time. > >Wh