Hi David,
On Sat, 4 Mar 2017 02:32:48 +1300, David Rowley
wrote:
>On 3 March 2017 at 18:26, George Neuner wrote:
>> I know most people here don't pay much - or any - attention to
>> SQLServer, however there was an interesting article recently regarding
>> significant performance differences be
On 3 March 2017 at 18:26, George Neuner wrote:
> I know most people here don't pay much - or any - attention to
> SQLServer, however there was an interesting article recently regarding
> significant performance differences between DISTINCT and GROUP BY as
> used to remove duplicates.
>
> https://s
On 03.03.2017 06:26, George Neuner wrote:
I know most people here don't pay much - or any - attention to
SQLServer, however there was an interesting article recently regarding
significant performance differences between DISTINCT and GROUP BY as
used to remove duplicates.
https://sqlperformance.c
Thank you.
I think there is no native function that will solve this problem,
intarray extension can't order as in the example query.
I could write a new SQL function but I solved the problem with
subqueries and works fine.
I need order because I use nested trees.
To_left and to_right are the
On 29 November 2015 at 20:51,
I
wrote:
> Well you could look at the intarray extension and a combination of
> array_agg, uniq() and
> *string_to_array*:
Mind blip, apologies, obviously I meant array_to_string :)
Geoff
On 29 November 2015 at 18:59, Sterpu Victor wrote:
> I can't skip the ordering.
> I'm sure aqjs3 is the one that produces the duplication.
> I guess subqueries are the only option, like this:
Well you could look at the intarray extension and a combination of
array_agg, uniq() and string_to_ar
1399031"
1399031;"1399032,1399033"
Is there a better way? I usualy try to avoid subqueries.
-- Original Message --
From: "Geoff Winkless"
To: "Sterpu Victor"
Cc: "PostgreSQL General"
Sent: 11/29/2015 6:42:18 PM
Subject: Re: [GENERAL] DISTINCT
On 28 November 2015 at 18:35, Sterpu Victor wrote:
> Can I make a distinct STRING_AGG?
> This is my query :
> SELECT atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY
> aqjs1.to_left) AS children
> FROM administration.ad_query_join_select atjs
> JOIN administration.ad_query aq ON (aq.id
On Nov 28, 2015, at 1:35 PM, Sterpu Victor wrote:
> Hello
>
> Can I make a distinct STRING_AGG?
> This is my query :
> SELECT atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY
> aqjs1.to_left) AS children
> FROM administration.ad_query_join_select atjs
> JOIN administration.ad_query
Alexander Reichstadt writes:
> following a query:
> SELECT DISTINCT ON (msgid) msgid FROM (SELECT refid_messages as msgid FROM
> messagehistorywithcontent WHERE 1=1 AND
> (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], '
> ')) LIKE '%gg%') ORDER BY messagekind DESC) as
Alexander Reichstadt writes:
> I think I solved it:
> SELECT * FROM (SELECT DISTINCT ON(refid_messages) refid_messages as msgid, *
> FROM messagehistorywithcontent WHERE
> (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], '
> ')) LIKE '%gg%') ORDER BY refid_messages DES
I think I solved it:
SELECT * FROM (SELECT DISTINCT ON(refid_messages) refid_messages as msgid, *
FROM messagehistorywithcontent WHERE
(lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' '))
LIKE '%gg%') ORDER BY refid_messages DESC) as foo ORDER BY messagekind ASC
Tha
Alexander Reichstadt wrote:
> SELECT
> DISTINCT ON (msgid)
> msgid
> FROM (
> SELECT refid_messages as msgid
> FROM messagehistorywithcontent
> WHERE 1=1
> AND
>(lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' '))
>LI
On 04/24/2013 09:03 AM, Alexander Reichstadt wrote:
The order is correct. Now from the outer SELECT I would expect then to get:
53
29
46
Please re-read the manual on DISTINCT ON.
"SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of
each set of rows where the given expressio
Em 27-11-2012 19:50, David Johnston escreveu:
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rodrigo Rosenfeld
Rosas
Sent: Tuesday, November 27, 2012 4:23 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] distinct values and count over windo
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rodrigo Rosenfeld
Rosas
Sent: Tuesday, November 27, 2012 4:23 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] distinct values and count over window function
Hello, this is my first post to thi
On 11/5/2011 4:11 PM, Tom Lane wrote:
Cstdenis writes:
If I understand that you are proposing as
select * from
(select distinct on (user_id) * from stories as s order
by user_id) as foo
order by date_submitted desc limit 10;
No, you always need to sort by *more
Cstdenis writes:
> If I understand that you are proposing as
> select * from
> (select distinct on (user_id) * from stories as s order
> by user_id) as foo
> order by date_submitted desc limit 10;
No, you always need to sort by *more* columns than are listed in
DISTI
On 11/5/2011 12:49 PM, Tom Lane wrote:
Cstdenis writes:
I am trying to write a query that selects recent submissions (sorted by
submission_date) but only selects the most recent one for each user_id.
example query: /select distinct on (user_id) * from stories order by
date_submitted desc limit
On 11/05/11 11:39 AM, Cstdenis wrote:
example query: /select distinct on (user_id) * from stories order by
date_submitted desc limit 10;/
select user_id,max(date_submitted) from stories group by date_submitted;
?
--
john r pierceN 37, W 122
santa cruz ca
Cstdenis writes:
> I am trying to write a query that selects recent submissions (sorted by
> submission_date) but only selects the most recent one for each user_id.
> example query: /select distinct on (user_id) * from stories order by
> date_submitted desc limit 10;/
> However postgres will n
On Sun, Nov 6, 2011 at 12:39 AM, Cstdenis wrote:
> I am trying to write a query that selects recent submissions (sorted by
> submission_date) but only selects the most recent one for each user_id.
>
> example query: select distinct on (user_id) * from stories order by
> date_submitted desc limit 1
On Tue, Apr 21, 2009 at 12:11:26PM +, Jasen Betts wrote:
> > Is there a way to acheive the above result without a sort and without a
> > self-join?
>
> anyway you could possibly write an agregate function that returns a
> copy of the row with the highest id?
Put that way it sounds like someth
On 2009-04-19, Martijn van Oosterhout wrote:
> Hi,
>
> I was going through the queries of an SQL application and came across
> queries like:
>
> SELECT * FROM foo
> WHERE id in (SELECT max(id) FROM foo GROUP BY bar);
>
> I thought, here's a case where this could be better written using
> DISTINCT
Martijn van Oosterhout writes:
> SELECT * FROM foo
> WHERE id in (SELECT max(id) FROM foo GROUP BY bar);
>
> Is there a way to acheive the above result without a sort and without a
> self-join?
Something like
SELECT bar, (magic_agg_func(foo)).* FROM foo GROUP BY bar
where you define an aggrega
* Richard Huxton:
> I take it SELECT DISTINCT bar... shows the same problem?
SELECT bar FROM baz does *not* show the duplicate row.
> If so, can you do:
> SELECT OID,xmin,cmin,xmax,cmax,bar FROM baz
> WHERE bar =
Even if I force a complete index scan, I get xmin = 1007617 for both
rows, the
* Tom Lane:
>> According to EXPLAIN, an index scan on the bar column is used (using
>> the underlying B-tree index).
>
> Do you mean an indexscan followed immediately by a Unique node? If
> so, yeah, that would depend entirely on correct ordering of the
> indexscan output to produce distinct resu
Florian Weimer <[EMAIL PROTECTED]> writes:
> I run this innocent query
> CREATE TABLE foo AS SELECT DISTINCT bar FROM baz ORDER BY bar;
> and the resulting table contains duplicate rows. 8-(
> According to EXPLAIN, an index scan on the bar column is used (using
> the underlying B-tree index).
Do
Florian Weimer wrote:
I run this innocent query
CREATE TABLE foo AS SELECT DISTINCT bar FROM baz ORDER BY bar;
and the resulting table contains duplicate rows. 8-(
According to EXPLAIN, an index scan on the bar column is used (using
the underlying B-tree index). This is with PostgreSQL 8.1.4
Christoph Pingel <[EMAIL PROTECTED]> writes:
> Am 08.08.2006 um 19:49 schrieb Nikolay Samokhvalov:
>> don't use "DISTINCT ON" at all, it's evil :-) (why?
>> http://chernowiki.ru/index.php?node=38#A13
> Thanks for the good advice! From reading this, it seems to be a
> *really* bad thing. And I di
Thanks for the input, I think I get this now. In my case, the querySELECT DISTINCT ON (substring(attribute from '^http://[^/]*/')) attribute from pg_atp where attribute like 'http://%' doesn't get me just the root of the URL, but the whole URL - but only for the first row for each individual root.
Am 08.08.2006 um 19:49 schrieb Nikolay Samokhvalov:
SELECT DISTINCT substring(attribute from '^http://[^/]*/') from pg_atp
where attribute like 'http://%';
w/o DISTINCT there should be duplicates (if any)
don't use "DISTINCT ON" at all, it's evil :-) (why?
http://chernowiki.ru/index.php?node=
DISTINCT ON is extremely useful when you know what you're doing. It's
postgres' version of oracle's first_value analytical function, and when
you need it, nothing else really suffices.
On Tue, 8 Aug 2006, Nikolay Samokhvalov wrote:
SELECT DISTINCT substring(attribute from '^http://[^/]*/') fr
SELECT DISTINCT substring(attribute from '^http://[^/]*/') from pg_atp
where attribute like 'http://%';
w/o DISTINCT there should be duplicates (if any)
don't use "DISTINCT ON" at all, it's evil :-) (why?
http://chernowiki.ru/index.php?node=38#A13)
On 8/8/06, Christoph Pingel <[EMAIL PROTECTED]
On Thu, Feb 09, 2006 at 09:51:13PM -0500, Stephen Frost wrote:
> * David Rio Deiros ([EMAIL PROTECTED]) wrote:
> > Now I have to redefine my query because I want to get the second
> > output but keeping the group_id. Ideas and suggestions are welcome.
>
> You might want to look at 'distinct on'.
* David Rio Deiros ([EMAIL PROTECTED]) wrote:
> Now I have to redefine my query because I want to get the second
> output but keeping the group_id. Ideas and suggestions are welcome.
You might want to look at 'distinct on'.
Stephen
signature.asc
Description: Digital signature
On Thu, Feb 09, 2006 at 07:20:19PM -0500, Tom Lane wrote:
> David Rio Deiros <[EMAIL PROTECTED]> writes:
> > I have some issues with the query attached at the end of this email.
> > If I run that query I got this output ( I have removed some of the
> > fields) despite the distinct clause:
>
> > Q
David Rio Deiros <[EMAIL PROTECTED]> writes:
> I have some issues with the query attached at the end of this email.
> If I run that query I got this output ( I have removed some of the
> fields) despite the distinct clause:
> QC Q&A | www.xxx.com | 44281
> QC Q&A | www.xxx.com | 44281
> WhyMAX?
Sorry I posted this to the wrong list. I have now reposted this is
pgsql-sql.
-Original Message-
From: Jeremy Palmer [mailto:[EMAIL PROTECTED]
Sent: Saturday, 19 November 2005 11:05 a.m.
To: 'pgsql-general@postgresql.org'
Subject: DISTINCT ON
Hi,
I have a table:
observation (
id in
On Fri, Dec 31, 2004 at 15:02:56 -0600,
[EMAIL PROTECTED] wrote:
>
> I've put an '*' next to the rows I want. So my dilemma is two part.
> First, I want to sort by the ordinal information only when the arc is
> pointing from the source object (id 638) to the other objects. Well, it's
> pretty
> Define the problem, not how you think it should be solved. What
> are you trying to do? If you can't get the query to work, then
> please post SQL statements to create and populate a table and
> describe the query results you'd like to see.
the situation is i have a set of records in a table (
On Fri, 31 Dec 2004 [EMAIL PROTECTED] wrote:
> It has come up several times on the various postgresql lists that in order
> to get around the requirement of DISTINCT ON parameters matching the first
> ORDER BY parameters, wrap the distinct query in a new 'order by' query:
>
> select * from (selec
On Fri, Dec 31, 2004 at 10:48:21AM -0600, [EMAIL PROTECTED] wrote:
> It has come up several times on the various postgresql lists that in order
> to get around the requirement of DISTINCT ON parameters matching the first
> ORDER BY parameters, wrap the distinct query in a new 'order by' query:
>
Stephan Szabo <[EMAIL PROTECTED]> writes:
> There really isn't a spec way to do it, however,
> select distinct on (col1) col1, col2, col3, col4 from tablename;
> would get you one row for each distinct col1, but it's undefined what
> row that gets you.
You can get a well-defined result with DIS
44 matches
Mail list logo
