Thread: Random sort with distinct

<div class="WordSection1"><p class="MsoNormal">I have the following query<p class="MsoNormal"> <p
class="MsoNormal">SelectDistinct VehicleMake, VehicleModel<p class="MsoNormal">From VehicleYearMakeModelTrim<p
class="MsoNormal">Orderby random()<p class="MsoNormal">Limit 10;<p class="MsoNormal"> <p class="MsoNormal">I don’t want
tobring back the random number I just want the sort order to be random. How can I sort randomly?  This query breaks
becauserandom() is not in the select.<p class="MsoNormal"> <p class="MsoNormal">Thanks<p class="MsoNormal"> <p
class="MsoNormal">Pam<pclass="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><br /><br
/></span></div>

This runs fine on my 8.4 install. What version are you using and what error message are you getting?<br /><br />
--Lee<br/><br /> On 10/01/2010 04:51 PM, Ozer, Pam wrote: <blockquote
cite="mid:216FFB77CBFAEE4B8EE4DF0A939FF1D10182C8@mail-001.corp.automotive.com"type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate{mso-style-priority:99;mso-style-link:"Balloon Text
Char";margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma","sans-serif";}
span.EmailStyle17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;}
span.BalloonTextChar{mso-style-name:"Balloon Text Char";mso-style-priority:99;mso-style-link:"Balloon
Text";font-family:"Tahoma","sans-serif";}
.MsoChpDefault{mso-style-type:export-only;}
@page WordSection1{size:8.5in 11.0in;margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1{page:WordSection1;}
--> </style><div class="WordSection1"><p class="MsoNormal">I have the following query<p class="MsoNormal"> <p
class="MsoNormal">SelectDistinct VehicleMake, VehicleModel<p class="MsoNormal">From VehicleYearMakeModelTrim<p
class="MsoNormal">Orderby random()<p class="MsoNormal">Limit 10;<p class="MsoNormal"> <p class="MsoNormal">I don’t want
tobring back the random number I just want the sort order to be random. How can I sort randomly?  This query breaks
becauserandom() is not in the select.<p class="MsoNormal"> <p class="MsoNormal">Thanks<p class="MsoNormal"> <p
class="MsoNormal">Pam<pclass="MsoNormal"><span style="font-size: 12pt; font-family: "Times New Roman","serif";"><br
/><br/></span></div></blockquote><br /><pre class="moz-signature" cols="72">-- 
 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
</pre>

"Ozer, Pam" <pozer@automotive.com> 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