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
Hi,
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 foo
This query rearranges the sor
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
Hi,
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 foo
This query rearranges the sor
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
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 not allow me to filter out
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
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 ON, since then you avoid the self-join:
SELECT DISTINCT ON (ba
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
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 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:
>
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 (select distinct on (a) a,b,c from foo order by a) order by c
25 matches
Mail list logo
