Thread

1. select random row from a group

   Thomas T. Thai <tom@minnesota.com> — 2003-04-08T06:59:30Z

   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
   
   
   

2. Instalation problem

   martin <martinb@sezampro.yu> — 2003-04-08T08:39:33Z

       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
   
   
   

3. Re: Instalation problem

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-04-08T08:49:55Z

   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
   
   
   

4. Re: select random row from a group

   Corey Scott <corey@motionworks.com.my> — 2003-04-09T03:30:48Z

   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]
   
   
   

5. Re: Instalation problem

   martin <martinb@sezampro.yu> — 2003-04-09T07:55:58Z

   
   
   > 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
   >
   
   
   

6. Re: Instalation problem

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-04-09T08:25:14Z

   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
   
   
   

7. Re: Instalation problem

   Mark Kirkwood <markir@paradise.net.nz> — 2003-04-09T08:46:25Z

   >
   >
   >  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