distinct order by random

I'm using pgsql
How do I get this to return distinct names ?


SELECT name FROM (items i LEFT JOIN items_category ic ON
i.id=ic.items_id) WHERE ic.isattop IS TRUE ORDER BY random()

If I try
DISTINCT SELECT name FROM (items i LEFT JOIN items_category ic ON
i.id=ic.items_id) WHERE ic.isattop IS TRUE ORDER BY random()

it says
Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must
appear in select list

which does not make any sense at all.

Re: distinct order by random

groups2@reenie.org wrote:
> I'm using pgsql
> How do I get this to return distinct names ?
>
>
> SELECT name FROM (items i LEFT JOIN items_category ic ON
> i.id=ic.items_id) WHERE ic.isattop IS TRUE ORDER BY random()
>
> If I try
> DISTINCT SELECT name FROM (items i LEFT JOIN items_category ic ON
> i.id=ic.items_id) WHERE ic.isattop IS TRUE ORDER BY random()
>
> it says
> Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must
> appear in select list
>
> which does not make any sense at all.

You can't order a SELECT DISTINCT by a column you didn't select. Which
does make sense - if the column you want to sort on is not in the select
list, it is not guaranteed to be distinct, so how can you order on it
when there could be multiple values of it?

In this case I think you will need a different approach here - because
if you add the random() to the select, then you will lose the
distinctness of the "name" (because each random() will be distinct).

i.e. `select distinct name, random() as rand from (etc)` won't work
because the pair (name, random()) will still be distinct even when name
is the same as an existing result.

I don't see a limit on your query, which tells me you want a randomly
sorted list of all distinct category names. I would probably just drop
the ORDER BY clause and do a shuffle() in your application logic if this
is the case (obviously if you are going to limit your result set then
this is not a good approach; look at the GROUP BY clause instead).

Jeremy

Re: distinct order by random

On 22 Apr, 01:21, grou...@reenie.org wrote:
> I'm using pgsql
> How do I get this to return distinct names ?
>
> SELECT name FROM (items i LEFT JOIN items_category ic ON
> i.id=ic.items_id) WHERE ic.isattop IS TRUE ORDER BY random()
>
> If I try
> DISTINCT SELECT name FROM (items i LEFT JOIN items_category ic ON
> i.id=ic.items_id) WHERE ic.isattop IS TRUE ORDER BY random()
>
> it says
> Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must
> appear in select list
>
> which does not make any sense at all.

Posting a question which has nothing to do with php in a php newsgroup
makes no sense at all either!