Re: [SQL] select question
On Wed, 28 Aug 2002 16:12:41 -0400 in message
<[EMAIL PROTECTED]>, george young <[EMAIL PROTECTED]> wrote:
> [postgreql 7.2, linux]
> I have a table T with columns run, wafer, and test:
>T(run text, wafer int, test text)
> Given a run and a set of wafers, I need the set of tests that match
> *all* the specified wafers:
>
> run wafer test
> a 1 foo
> a 2 foo
> a 3 foo
> a 3 bar
>
> E.g.
> Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches
>both 1 and 3.
> Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and
>bar match 3.
>
> Is there some neat way to do this in a single query?
>
select test from T
where run='a'
and wafers in ('1','3')
group by test
eric
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] select question
You can use this query SELECT * FROM T WHERE run = 'a' AND wafer = 1 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 2 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 3 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 3 AND test = 'bar' On Wed, 2002-08-28 at 16:12, george young wrote: > [postgreql 7.2, linux] > I have a table T with columns run, wafer, and test: >T(run text, wafer int, test text) > Given a run and a set of wafers, I need the set of tests that match > *all* the specified wafers: > > run wafer test > a 1 foo > a 2 foo > a 3 foo > a 3 bar > > E.g. > Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches >both 1 and 3. > Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and >bar match 3. > > Is there some neat way to do this in a single query? > > Puzzled, > George > > > -- > I cannot think why the whole bed of the ocean is > not one solid mass of oysters, so prolific they seem. Ah, > I am wandering! Strange how the brain controls the brain! > -- Sherlock Holmes in "The Dying Detective" > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select question
[postgreql 7.2, linux] I have a table T with columns run, wafer, and test: T(run text, wafer int, test text) Given a run and a set of wafers, I need the set of tests that match *all* the specified wafers: run wafer test a 1 foo a 2 foo a 3 foo a 3 bar E.g. Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches both 1 and 3. Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and bar match 3. Is there some neat way to do this in a single query? Puzzled, George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Detective" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Select question
"Chris Ruprecht" <[EMAIL PROTECTED]> writes: > phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit > 2 ; Try select * from phonelog where cdate > '2001-05-18' order by cdate limit 2 I think it's interpreting your query as where cdate > 1978 (result of integer subexpression) and then doing some weird integer-to-date conversion. In general, any constant of a non-numeric datatype needs to be quoted in SQL queries. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Select question
I'm not sure, but... Does it work if you say cdate > '2001-05-18' ? (Possibly ::date too) I'd guess your date value you're trying to put there is getting treated as an integer expression. On Wed, 23 May 2001, Chris Ruprecht wrote: > Hi all, > > although not new to databases, I'm new to the wonderful world of PostGreSQl > and SQL in general. > Question: > > I do this query > phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit > 2 ; > > And I get theis result > >cdate| ctime | countrycode | success | carrier | duration | > phonenumber | areacode | pseq > +---+-+-+-+--+-- > ---+--+-- > 2001-04-01 | 0 | 370 | 1 | 1 |8 | "3703348" > | "33" | 4005 > 2001-04-01 | 0 | 98 | 1 | 1 | 15 | "9871162" > | "71" | 3889 > > > Although I specified that I want only dates > 5/18/2001, I get dates > 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this > question the correct way? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Select question
Hi all, although not new to databases, I'm new to the wonderful world of PostGreSQl and SQL in general. Question: I do this query phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit 2 ; And I get theis result cdate| ctime | countrycode | success | carrier | duration | phonenumber | areacode | pseq +---+-+-+-+--+-- ---+--+-- 2001-04-01 | 0 | 370 | 1 | 1 |8 | "3703348" | "33" | 4005 2001-04-01 | 0 | 98 | 1 | 1 | 15 | "9871162" | "71" | 3889 Although I specified that I want only dates > 5/18/2001, I get dates 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this question the correct way? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
