From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | terry(at)greatgulfhomes(dot)com |
Cc: | "Postgres (E-mail)" <pgsql-docs(at)postgresql(dot)org> |
Subject: | Re: DISTINCT and ORDER BY issue |
Date: | 2002-05-23 18:17:32 |
Message-ID: | 1022177854.10286.7.camel@rh72.home.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
On Thu, 2002-05-23 at 19:49, terry(at)greatgulfhomes(dot)com wrote:
> I know what I am doing wrong: The order by needs to match the DISTINCT
> fields.
>
> Here's what I am doing: Get the 10 most recent applications user has used
> to put in their "recently used" menu.
>
> So I need to ORDER BY user_app_access_log.access_stamp DESC
>
> But I want each record distinct, eg if the last 5 hits were all for the same
> app, I want 1 record returned for that. So if I put the access_stamp field
> in the DISTINCT field set, then I go back to getting repeats of apps, hence
> not 10 unique app names...
>
> I wonder if there is a way to use an aggregate function to just return the
> first value, (ignoring the rest of the group), and use the group by clause
> on the app_name?
>
> Any ideas is appreciated.
>
> Here is my query/error message:
> Error while executing the query; ERROR: For SELECT DISTINCT, ORDER BY
> expressions must appear in target list
>
>
> SELECT DISTINCT applications.app_name, applications.app_long_name,
> applications.app_url, user_access.in_new_window
> FROM applications, user_access, user_app_use_log
> WHERE user_app_use_log.user_id = '1'
> AND user_app_use_log.app_name != 'index'
> AND user_access.app_name = user_app_use_log.app_name
> AND user_access.user_id = user_app_use_log.user_id
> AND user_access.division_id = 'GGH'
> AND applications.app_name = user_access.app_name
> ORDER BY user_app_use_log.access_stamp DESC
> LIMIT 10"
You can try either
SELECT DISTINCT app_name,app_long_name,app_url,in_new_window
FROM (
SELECT
user_app_use_log.access_stamp,
applications.app_name,
applications.app_long_name,
applications.app_url,
user_access.in_new_window
FROM applications, user_access, user_app_use_log
WHERE user_app_use_log.user_id = '1'
AND user_app_use_log.app_name != 'index'
AND user_access.app_name = user_app_use_log.app_name
AND user_access.user_id = user_app_use_log.user_id
AND user_access.division_id = 'GGH'
AND applications.app_name = user_access.app_name
ORDER BY user_app_use_log.access_stamp DESC
) t
LIMIT 10
;
or
SELECT
applications.app_name,
applications.app_long_name,
applications.app_url,
user_access.in_new_window,
max(access_stamp) as max_access_stamp
FROM applications, user_access, user_app_use_log
WHERE user_app_use_log.user_id = '1'
AND user_app_use_log.app_name != 'index'
AND user_access.app_name = user_app_use_log.app_name
AND user_access.user_id = user_app_use_log.user_id
AND user_access.division_id = 'GGH'
AND applications.app_name = user_access.app_name
GROUP BY 1,2,3,4
ORDER BY max_access_stamp DESC
LIMIT 10
;
and see which one is more effective
---------------
Hannu
From | Date | Subject | |
---|---|---|---|
Next Message | Marcia Abade | 2002-05-29 13:49:26 | HOW TO APPLY PATCHES |
Previous Message | terry | 2002-05-23 14:49:07 | DISTINCT and ORDER BY issue |