Hello,
I'd like to select from one table ( contains duplicates ) into a new table.
However the new table is exactly the same as the original ( duplicates
included )
I think I have a problem with my DISTINCT clause - sql below.
It seems the DISTINCT is applied to the whole row not just the 'phon
Hi,
I'm trying to retreive DISTINCT Values from a two colomn table called
"Books". The colomns are named "Author" and "URL".
DISTINCT values should be retieved from the "Author" Colomn , and then I
should be able to retrieve the corresponding URL.
How do I build the SQL Query ?
I tried to use: "S
> SELECT
> DISTINCT phone,
> company_name,
> address,
> state,
> idc_desc_1,
> street_number,
> street_name,
> street_xtra,
> suburb,
> postcode,
> area_code,
> ac_phone,
> fax_area_code,
> fax_phone,
> fax_ac_phone,
> latitude,
>
Christopher,
Thank you.
I though if I'm lucky Chris will be online and will see that one.
Kind regards
Rudi.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere"
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
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)
Hi there,
does this look right?
FAKDB=# select count(distinct(f.land)) from firmen f where
f.typlist='Redaktion';
count
---
1975
(1 row)
FAKDB=# select count(distinct(f.land||'1')) from firmen f where
f.typlist='Redaktion';
count
---
4944
(1 row)
FAKDB=# select version();
Hi,
I'm having difficulty figuring this one out:
I have a table with record entries which all have a date attribute, looking
something like '2000-11-11'. What I want from my data base is a list of
all the years that are present in this attribute in the relation. So for
example, if I had a cou
On Wed, 30 May 2001, Nicolas wrote:
> Hi,
>
> I'm trying to retreive DISTINCT Values from a two colomn table called
> "Books". The colomns are named "Author" and "URL".
> DISTINCT values should be retieved from the "Author" Colomn , and then I
> should be able to retrieve the corresponding URL.
> Author URL
> ---
> A http://www.xyz http://www.abc .com
> - -
> B http://www.def http://www.ijk http://www.rst .com
> - -
>
> If that is what you want, you cannot manage it.
Sure, in no way this is possible with SQL (in
If art_id is the primary key of the la table, is the latter faster?
SELECT DISTINCT la.* FROM ...
or
SELECT DISTINCT ON (la.art_id) la.* FROM ...
ie. Does this offer a performance improvement by only doing the distinct on
the primary key or not?
Thanks,
Chris
---(
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,
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 $
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
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
-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
> -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
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.
-
I need to list products from the table of ordered products with ordered
quantities. Each product should be there only once but quantity should be a sum
from all the same products from the table.
I prepared some query but in quantity column I can see only amount from one
record. I need to have su
On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
wrote:
> Hi there,
>
> does this look right?
>
> FAKDB=# select count(distinct(f.land)) from firmen f where
> f.typlist='Redaktion';
> count
> ---
> 1975
> (1 row)
>
> FAKDB=# select count(distinct(f.land||'1')) from firmen f where
> f.typl
am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com:
> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
> wrote:
>> Hi there,
>>
>> does this look right?
>>
>> FAKDB=# select count(distinct(f.land)) from firmen f where
>> f.typlist='Redaktion';
>> count
>> ---
>>
On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania
wrote:
> am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com:
>
>> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
>> wrote:
>>> Hi there,
>>>
>>> does this look right?
>>>
>>> FAKDB=# select count(distinct(f.land)) f
Is firmen a table or a view?
From: Scott Marlowe
To: Maximilian Tyrtania
Cc: pgsql-sql@postgresql.org
Sent: Friday, May 8, 2009 5:35:21 AM
Subject: Re: [SQL] Distinct oddity
On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania
wrote:
> am 07.05.2009 20
Maximilian Tyrtania writes:
> am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com:
>> Yeah, that does seem odd. Could it be something like nulls in your
>> data set? just guessing really. If you could make a small test case
>> that shows it happening and allows others to
am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsarg...@rocketmail.com:
>Is firmen a table or a view?
It's a table.
am 08.05.2009 21:52 Uhr schrieb Tom Lane unter t...@sss.pgh.pa.us:
>> It doesn't seem to be related to null values (which wouldn't explain it
>> anyway) nor to this particula
Maximilian Tyrtania writes:
> FAKDB=# select distinct f.bezeichnung from firmen f order by 1 limit 5
> FAKDB-# ;
>bezeichnung
> -
> sterreichisches Verkehrsbro AG
> \x01Assistenz
> \x10Frohstoff Design & Textilveredelung
> "1. Mittels
On Sat, May 9, 2009 at 10:33 AM, Tom Lane wrote:
> That only proves that adding the 'e' changes the sort order, which is
> completely unsurprising for any non-C locale. What you need to do is
> dump out the *entire* results of the DISTINCT queries and look for the
> unmatched lines. I'd try dump
Maximilian Tyrtania wrote:
> am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsarg...@rocketmail.com:
> > and what locale are you running in?
>
> lc_collate | de_DE
> | Shows the collation order locale.
> lc_ctype| de_DE
> | Shows the character c
am 10.05.2009 4:58 Uhr schrieb Alvaro Herrera unter
alvhe...@commandprompt.com:
>>> and what locale are you running in?
>>
>> lc_collate | de_DE
>> | Shows the collation order locale.
>> lc_ctype| de_DE
>> | Shows the character classification and cas
Maximilian Tyrtania wrote:
> am 10.05.2009 4:58 Uhr schrieb Alvaro Herrera unter
> alvhe...@commandprompt.com:
> > I'd try checking if the problem is reproducible in
> > de_DE.utf8 (you need to create a new database for testing, obviously).
>
> Wait a minute. I need to re- initdb with de_DE.UTF-8
Maximilian Tyrtania wrote:
> am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter
> alvhe...@commandprompt.com:
>
> >>>Note that the de_DE locale uses Latin9 encoding, which is incompatible
> >>>with UTF8.
> >>> I'd try checking if the problem is reproducible in
> >>> de_DE.utf8 (you need to crea
am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter
alvhe...@commandprompt.com:
>>>Note that the de_DE locale uses Latin9 encoding, which is incompatible
>>>with UTF8.
>>> I'd try checking if the problem is reproducible in
>>> de_DE.utf8 (you need to create a new database for testing, obviously)
am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter
alvhe...@commandprompt.com:
>> FAKDB=# CREATE DATABASE "TestLatin9"
>> FAKDB-# WITH ENCODING='LATIN9'
>> FAKDB-#OWNER=postgres;
>> ERROR: encoding LATIN9 does not match server's locale de_DE
>> DETAIL: The server's LC_CTYPE setting r
am 09.05.2009 16:33 Uhr schrieb Tom Lane unter t...@sss.pgh.pa.us:
> What you need to do is
> dump out the *entire* results of the DISTINCT queries and look for the
> unmatched lines. I'd try dumping to two files, stripping the 'e' with
> sed, and then sort/diff.
Okay, that's what I did, and the
Maximilian Tyrtania writes:
> am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter
> alvhe...@commandprompt.com:
>> What platform are you using anyway?
> Mac OS 10.4.11
I have some vague recollection that UTF8-using locales don't actually
work well on OSX ... check the archives ...
I wrote:
> Maximilian Tyrtania writes:
>> am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter
>> alvhe...@commandprompt.com:
>>> What platform are you using anyway?
>> Mac OS 10.4.11
> I have some vague recollection that UTF8-using locales don't actually
> work well on OSX ... check the archive
For purposes of DISTINCT, I'd expect any sort order should do; all it
needs is for equal values to be grouped together. If strcoll() ever
fails to do that, I'd call it a critical bug--even throwing total
garbage at it should result in a consistent ordering, even if the
ordering itself is totally m
Glenn Maynard writes:
> For purposes of DISTINCT, I'd expect any sort order should do; all it
> needs is for equal values to be grouped together. If strcoll() ever
> fails to do that, I'd call it a critical bug--even throwing total
> garbage at it should result in a consistent ordering, even if t
This should work:
SELECT distinct(date_part('year', )) FROM <>;
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Sun, 25 Feb 2001, Lotus118 wrote:
> Hi,
> I'm having difficulty figuring this one out:
> I have a table with record entries which all have a
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
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
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
( 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
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",
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
I need to list products from the table of ordered products with ordered
quantities. Each product should be there only once but quantity should be a
sum from all the same products from the table.
I prepared some query but in quantity column I can see only amount from
one record. I need to have su
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
Hi Everyone,
I am having some issues trying to create the required SQL that will
allow me to return the results I am after.
I have been trying various incarnations, using group by, sub-queries -
albeit to no avail - for the past three hours.
Consider the following simple SQL;
select
a
[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
I have a table indexed on a char(35) field and want a query to return a
distinct list of the values in this column. Is there some syntactical
magic I can do to get these values without a sequential scan? I assume,
( here we go again ) that these values are in the index somewhere and I
seem to rec
"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
[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
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
: 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***
"Gavin 'Beau' Baumanis" <[EMAIL PROTECTED]> writes:
> ... If there a multiple rows of the same id in table1, I get all
> (multiple) rows - as you would expect - of course.
> What I need however, is only one row returned per instance a.id that
> is returned by the above query.
You need GROUP B
Hi Tom I am a nut.
please find below my correct requirements.
select
a.foo,
b.bar,
c.something
from
table1 a,
inner join
table2 b on b.id =a.id
left outer join
table3 on c.id = a.id
If there a multiple rows of the same id in table2,(one (a) to many (b)
relatio
Bret,
> I have a table indexed on a char(35) field and want a query to return a
> distinct list of the values in this column. Is there some syntactical
> magic I can do to get these values without a sequential scan? I assume,
> ( here we go again ) that these values are in the index somewhere an
What does The SQL Standard say about this one?
create table foo (a varchar, b int);
insert into foo (a, b) select null, null from bar;
-- no problem
insert into foo (a, b) select distinct null, null from bar;
-- ERROR: column "b" is of type integer but expression is of type text
-- HINT: You w
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-
"George Pavlov" <[EMAIL PROTECTED]> writes:
> What does The SQL Standard say about this one?
> insert into foo (a, b) select distinct null, null from bar;
> -- ERROR: column "b" is of type integer but expression is of type text
According to the SQL spec that query is illegal on its face --- the
> "Better" is in the eye of the beholder.
sorry for the value-laden term. "laxer" is more appropriate, of course!
the funny thing is that had they cast the NULLs to TEXT it would have
failed there too (they do not do implicit TEXT to INT).
> It surprises me not at all that
> Microsoft would be
"George Pavlov" <[EMAIL PROTECTED]> writes:
> indeed! but, wait, doesn't our favorite dbms do some implicit casting
> too? continuing with my table foo (a varchar, b int):
> test=# delete from foo;
> DELETE 2
> test=# insert into foo values (4,4);
> INSERT 0 1
> test=# insert into foo values ('4',
> Sure, but in this example the required type of the value is clear from
> immediate context (ie, the INSERT). This is one of the cases where
> the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4'
> in this example are *not* values of type text; they are
> untyped literals which
George Pavlov wrote:
> test=# insert into foo values (4::int,4::int);
> INSERT 0 1
> test=# insert into foo values (4::text,4::text);
> ERROR: column "b" is of type integer but expression is of type text
> HINT: You will need to rewrite or cast the expression.
> test=# insert into foo values (ca
> > test=# insert into foo values (4::int,4::int);
> > INSERT 0 1
> > test=# insert into foo values (4::text,4::text);
> > ERROR: column "b" is of type integer but expression is of type text
> > HINT: You will need to rewrite or cast the expression.
> > test=# insert into foo values (cast(4 as
"George Pavlov" <[EMAIL PROTECTED]> writes:
> the interesting thing here is that 4::int gets into a text
> field whereas 4::text does not get into an integer field. seems to me
> like there is an implicit int-to-text cast (without a symmetrical
> text-to-int one)
Yeah, there is. You can easily se
I'm work with Sybase on the Sun... and, being a clueles newbee in SQL
department, hope you could help. I have a set of data:
acct name qty link date memo
101 item_A 100 0001 9/2/02 blah
101 item_A 250 0001 9/3/02 n/a
101 item_A80 0002 9/3/02 n/a
101 item_B90 000
"RVL" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> I'm work with Sybase on the Sun... and, being a clueles newbee in SQL
> department, hope you could help. I have a set of data:
>
> acct name qty link date memo
> 101 item_A 100 0001 9/2/02 blah
Dear List,
i want to built an aggregate function wich should summing up values in
a distinct manner:
dsum(DISTINCT ROW(value, distinction)).
I implemented all things i thought i need but it dont work. i get
always an 'could not identify an equality operator for type
dsum_type'-error nevertheles
using DISTINCT with complex types depends on an OPERATOR CLASS for
B-Tree-Indexes i found out. the error msg 'could not identify an
equality operator for type' was confusing.
i post this complete example. maybe someone else is running in this problem too.
regards,
thomas!
CREATE TYPE named_va
70 matches
Mail list logo
