[SQL] Random sort with distinct

2010-10-02 Thread Ozer, Pam 
I have the following query

 

Select Distinct VehicleMake, VehicleModel

>From VehicleYearMakeModelTrim

Order by random()

Limit 10;

 

I don't want to bring back the random number I just want the sort order
to be random. How can I sort randomly?  This query breaks because
random() is not in the select.

 

Thanks

 

Pam

Re: [SQL] Random sort with distinct

2010-10-02 Thread Dmitriy Igrishin 
Hey Ozer,

How about dynamic queries?

2010/10/2 Ozer, Pam 

>  I have the following query
>
>
>
> Select Distinct VehicleMake, VehicleModel
>
> From VehicleYearMakeModelTrim
>
> Order by random()
>
> Limit 10;
>
>
>
> I don’t want to bring back the random number I just want the sort order to
> be random. How can I sort randomly?  This query breaks because random() is
> not in the select.
>
>
>
> Thanks
>
>
>
> Pam
>
>
>
>


-- 
// Dmitriy.

Re: [SQL] Random sort with distinct

2010-10-02 Thread Lee Hachadoorian 




This runs fine on my 8.4 install. What version are you using and what
error message are you getting?

--Lee

On 10/01/2010 04:51 PM, Ozer, Pam wrote:

  
  
  

  
  I have the following query
   
  Select Distinct VehicleMake, VehicleModel
  From VehicleYearMakeModelTrim
  Order by random()
  Limit 10;
   
  I don’t want to bring back the random number I
just
want the sort order to be random. How can I sort randomly?  This query
breaks
because random() is not in the select.
   
  Thanks
   
  Pam
  
  
  
  


-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

Re: [SQL] Random sort with distinct

2010-10-02 Thread Tom Lane 
"Ozer, Pam"  writes:
> Select Distinct VehicleMake, VehicleModel
> From VehicleYearMakeModelTrim
> Order by random()
> Limit 10;

> I don't want to bring back the random number I just want the sort order
> to be random. How can I sort randomly?  This query breaks because
> random() is not in the select.

Well, yeah: the implication of the ORDER BY is that a new random value
is to be computed for each row of VehicleYearMakeModelTrim.  After you
combine rows with DISTINCT it's not clear which of those values should
be used to sort a grouped row.

You need to put the DISTINCT and the ORDER BY in separate query levels,
like this:

select * from
  (Select Distinct VehicleMake, VehicleModel
   From VehicleYearMakeModelTrim) ss
Order by random()
Limit 10;

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql