From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Ozer, Pam" <pozer(at)automotive(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Random sort with distinct |
Date: | 2010-10-02 13:52:50 |
Message-ID: | 5070.1286027570@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Ozer, Pam" <pozer(at)automotive(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2010-10-03 18:54:41 | join returns too many results... |
Previous Message | Lee Hachadoorian | 2010-10-02 13:42:29 | Re: Random sort with distinct |