SQL discussion

Ozer, Pam | 1 Oct 2010 22:51

Random sort with distinct

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

headers

Dmitriy Igrishin | 2 Oct 2010 15:40

Re: Random sort with distinct

Hey Ozer,

How about dynamic queries?

2010/10/2 Ozer, Pam <pozer <at> automotive.com>

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.

headers

Ozer, Pam | 4 Oct 2010 17:40

Re: Random sort with distinct

What about dynamic queries?

From: pgsql-sql-owner <at> postgresql.org [mailto:pgsql-sql-owner <at> postgresql.org] On Behalf Of Dmitriy Igrishin
Sent: Saturday, October 02, 2010 6:40 AM
To: Ozer, Pam
Cc: pgsql-sql <at> postgresql.org
Subject: Re: [SQL] Random sort with distinct

Hey Ozer,

How about dynamic queries?

2010/10/2 Ozer, Pam <pozer <at> automotive.com>

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.

headers

Lee Hachadoorian | 2 Oct 2010 15:42

Re: Random sort with distinct

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

headers

Tom Lane | 2 Oct 2010 15:52

Re: Random sort with distinct

"Ozer, Pam" <pozer <at> 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

--

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