This has been resolved -- although I still think it may be a bug in
Postgres.
I'm confused as hell, it's Friday, and it's hot though... So I'll have to
think about it over the weekend and let you know if I can make sense of it.
Thanks all for your suggestions.
Cheers,
~p
-Original Message-
: pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON not working...?
Wild guess - have you tried reindexing this table? I haven't seen
corrupted indexes since 7.1, though - it usually means subtle hardware
problems.
***Confidentiality and Privilege Notice***
Dnia Tue, 20 Feb 2007 15:36:32 +1100, Phillip Smith napisał(a):
> Removing the CASE statement all together:
> SELECT DISTINCT ON (ean)
> ean,
> count(*)
> FROMTMPTABLE
> WHERE code NOT IN (SELECT code FROM stock_deleted)
> ANDean IS NOT NULL
> GROUP BY ean
>
> Still gi
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, 20 February 2007 15:33
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON not working...?
Perhaps you've confused yourself by using "ean" as both an input and an
output column name? I think that the "ean
"Phillip Smith" <[EMAIL PROTECTED]> writes:
> To cut out all the details, the code that is causing the problem:
> SELECT DISTINCT ON (ean)
> code,
> CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
> null ELSE ean END AS ean
> FROMTMPTABL
Hi all,
Strange one - I have a nightly export / import routine that exports from one
database and imports to another. Has been working fine for several months,
but last night it died on a unique constraint.
To cut out all the details, the code that is causing the problem:
SELECT DISTINCT
On Sat, Nov 19, 2005 at 17:06:27 +1300,
Jeremy Palmer <[EMAIL PROTECTED]> wrote:
>
> Interesting enough, on my server the "distinct on" clause that I originally
> ran takes 10% of execution time that the query you provided does.
If DISTINCT ON wasn't useful, I doubt it would have been added.
-
> -Original Message-
> From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
> Sent: Saturday, 19 November 2005 4:07 p.m.
> To: Jeremy Palmer
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] DISTINCT ON
>
> [Please don't top post. It makes the post harder
-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
Sent: Saturday, 19 November 2005 12:28 p.m.
On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote:
SELECT DISTINCT ON (vector_id, obs_type)
id
FROM observation
ORDER BY vector_id,
obs_type,
date
further suggestions?
-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
Sent: Saturday, 19 November 2005 12:28 p.m.
To: Jeremy Palmer
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON
On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote:
> SELECT DISTINCT
On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote:
SELECT DISTINCT ON (vector_id, obs_type)
id
FROM observation
ORDER BY vector_id,
obs_type,
date DESC;
However the documentation also states that "DISTINCT ON" is not
part of the
SQL standard and should be avoided when po
Hi,
I have a table:
observation (
id int4 NOT NULL [PRIMARY KEY],
vector_id NOT NULL [FORGIEN KEY],
obs_type VARCHAR(4) NOT NULL,
date TIMESTAMP NULL
)
I need to select the newest observation id, classify by type, for each
vector (there can be multiple observation ids on each vector)
On Thu, 19 Feb 2004, scott.marlowe wrote:
> On Thu, 19 Feb 2004, Brian Knox wrote:
>
> > ( sorry if this is a repeat, my mail server is being wonky today )
> >
> > I'm looking for a way, within SQL, given a starting date and an ending
> > date, to get back the number of months between the start
On Thu, 19 Feb 2004, Brian Knox wrote:
> ( sorry if this is a repeat, my mail server is being wonky today )
>
> I'm looking for a way, within SQL, given a starting date and an ending
> date, to get back the number of months between the start and end date.
> If I "SELECT end_date - start_date",
( sorry if this is a repeat, my mail server is being wonky today )
I'm looking for a way, within SQL, given a starting date and an ending
date, to get back the number of months between the start and end date.
If I "SELECT end_date - start_date", I get back an interval in days; I
need months.
T
Thanks Josh,
I'll do that, I just wasn't sure if I was missing something obvious.
Jeremy
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 19, 2004 2:29 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] DISTINCT ON trouble
Jeremy,
> Am I just approaching this all wrong and need to create a temporary table
> and draw from that, or is there a way to salvage this query?
Think about using a subquery instead of the DISTINCT ON approach. I don't
think you can get what you want with DISTINCT ON.
A temporary table is n
Hi,
I have a query that reads:
SELECT DISTINCT ON (messageboard.threadid) messageboard.threadid,
messageboard.topic, owner.ownerid, owner.username FROM messageboard, owner
WHERE messageboard.ownerid=owner.ownerid AND messageboard.leagueid =
'$leagueid' ORDER BY messageboard.messageid DESC LIMIT $
Don't know. I've seen it elsewhere so it might even be "standard".
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic
> Sent: Friday, 13 September 2002 10:50 AM
> To: [EMAIL PROTECTED]
>
Hi,
Just quickly, DISTINCT ON - Is that Postgresql only ?
Thanks
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[EMAIL PROTECTED] writes:
> How can you use a distinct on () including the whole union.
In 7.1 you can write
select distinct ... from (select ... union select ...) ss;
regards, tom lane
---(end of broadcast)---
TIP 3: if p
How can you use a distinct on () including the whole union.
eg
select distinct on (valutaid) valutaid, short from valuta UNION select
landid, land from land order by valutaid;
table: valuta
valutaidvaluta
1 USD
2 SEK
table: land
landid land
1
22 matches
Mail list logo
