Re: DISTINCT and ORDER BY issue
От | Hannu Krosing |
---|---|
Тема | Re: DISTINCT and ORDER BY issue |
Дата | |
Msg-id | 1022177854.10286.7.camel@rh72.home.ee обсуждение исходный текст |
Ответ на | DISTINCT and ORDER BY issue (terry@greatgulfhomes.com) |
Список | pgsql-docs |
On Thu, 2002-05-23 at 19:49, terry@greatgulfhomes.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
В списке pgsql-docs по дате отправления: