From: terry@greatgulfhomes.com
To: Postgres (E-mail) <pgsql-docs@postgresql.org>
Subject: DISTINCT and ORDER BY issue
Date: Thu, 23 May 2002 10:49:07 -0400
Message-ID: <001401c20268$fdff0c40$2766f30a@development.greatgulfhomes.com> (raw)
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"
Thanks in advance
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: terry@greatgulfhomes.com
Subject: Re: DISTINCT and ORDER BY issue
In-Reply-To: <001401c20268$fdff0c40$2766f30a@development.greatgulfhomes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox