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