pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

DISTINCT and ORDER BY issue
2+ messages / 2 participants
[nested] [flat]

* DISTINCT and ORDER BY issue
@ 2002-05-23 14:49  terry@greatgulfhomes.com
  0 siblings, 1 reply; 2+ messages in thread

From: terry@greatgulfhomes.com @ 2002-05-23 14:49 UTC (permalink / raw)
  To: pgsql-docs

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: DISTINCT and ORDER BY issue
@ 2002-05-23 18:17  Hannu Krosing <hannu@tm.ee>
  parent: terry@greatgulfhomes.com
  0 siblings, 0 replies; 2+ messages in thread

From: Hannu Krosing @ 2002-05-23 18:17 UTC (permalink / raw)
  To: terry@greatgulfhomes.com; +Cc: 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













^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-05-23 18:17 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-23 14:49 DISTINCT and ORDER BY issue terry@greatgulfhomes.com
2002-05-23 18:17 ` Hannu Krosing <hannu@tm.ee>

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