Thread: select random row from a group

I'd like to be able to select one random row from each group using one
select statement.

CREATE TABLE randtest (
  catnum int,
  title varchar(32)
);

INSERT INTO randtest VALUES (1, 'one.one');
INSERT INTO randtest VALUES (1, 'one.two');
INSERT INTO randtest VALUES (1, 'one.three');
INSERT INTO randtest VALUES (2, 'two.one');
INSERT INTO randtest VALUES (2, 'two.two');
INSERT INTO randtest VALUES (2, 'two.three');
INSERT INTO randtest VALUES (3, 'three.one');
INSERT INTO randtest VALUES (3, 'three.two');
INSERT INTO randtest VALUES (3, 'three.three');

Something along the line of:

SELECT catnum, title, MAX(RAND()) as r
FROM randtest
WHERE 1=1
GROUP BY catnum;

--
Thomas T. Thai

    Hi, I'm a newbie in PostgreSQL. I have 7.3.1 and a Slackware 8.0. When I
start instalation , here's what happens:

   ./configure

this is as far as I get, after this I'm getting a message bad interpreter or
there's no such file. Is my linux to old or ... ?

Martin

martin wrote:
 >
 > this is as far as I get, after this I'm getting a message bad
interpreter or
 > there's no such file.

What's the exact message?

 > Is my linux to old or ... ?

Maybe you are just missing some needed packages, like a compiler. Give
us the error message and we can point you in the right direction.

Jean-Christian Imbeault

Thomas，

The following is an approach to select randomly one record from the
whole lot.  Perhaps you can get it to do what you want.  But I would
recommend, either splitting the different in different tables (at least
temp tables for this queries).

Steps:
1) Add and AUTO_INCREMENT column to the table (eg. recordID type=int)
2) Try this select statement:
    SELECT *
    FROM randtest
    WHERE recordID = ( FLOOR( RAND() * MAX( recordID ) + 1 ) )

Points to note:
    -The RAND result is multiplied by the max recordID (you might
find counting the records easier, as this will error, if the index has
missing records)
    - I have added the +1, so that the result of the Floor never
returns 0, you can use CEIL if you wish it is the same difference, just
remove the +1

Hope this helps.

Corey Scott
/* ================================================================ *\
|    "My life is spent in one long effort to escape from the
|
|    common places of existence.  These little problems
help            |
}    me to do so."               -Sherlock
Holmes                    |
\* ================================================================ */


---
[This E-mail scanned for viruses by Declude antiVirus]

> martin wrote:
>  >
>  > this is as far as I get, after this I'm getting a message bad
> interpreter or
>  > there's no such file.
>
> What's the exact message?

  The exact message is :  bash: ./configure: bad interpreter: No such file
or directory

  I've tried to make my own configure file with autoconf, but I get a
message that my autoconf is out of date for configure.in:
  configure.in: 29 error: Autoconf version 2.53 or higher is required for
this scr ipt
  configure.in: 29: the top level

 My version of 2.95.3


> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

Yes, but I didn't look too long because I don't have unlimited aproach to
Internet from my home and I was at the end of my time :).
>
> http://archives.postgresql.org
>

martin wrote:
>
>   The exact message is :  bash: ./configure: bad interpreter: No such file
> or directory


Just a gues but this seems to mean that the correct shell interpreter
for the script cannot be found. Which is strange since you are using
bash ...

Is the first line of your configure file:

#! /bin/sh

If not that would be a problem ...

If it is what do you get if you do:

$ which sh

You should get something like:

$ which sh
/bin/sh

If you get something like:

/usr/bin/which: no sh in .....

Then there is something strange about your system ;)

Jean-Christian Imbeault

>
>
>  The exact message is :  bash: ./configure: bad interpreter: No such file
>or directory
>
>
Where is your sh installed ?

Postgresql's version of  configure expects it to be in /bin  :

[postgres@localhost]$ head -1 configure
#! /bin/sh

This may not actually be the problem, but its worth checking (Slackware
might install sh in /usr/bin for all I know...)

regards

Mark