Hi there,
I need to know the best way to get a random subset
of records without duplicates in the resulting record
set using a Postgresql database query with Livecode
on On-Rev.
In other words, I don't want to have to get all of the
records in the database first just to be able to pick
out the
It's a SQL syntax task, not a revDB one and as soon as the query will be OK,
revDB will just handle it as expected.
http://archives.postgresql.org/pgsql-general/2007-08/msg02034.php
http://bytes.com/topic/postgresql/answers/857541-random-sample-rows
Best regards,
Le 2 févr. 2011 à 21:29, Rick
Hi Pierre,
What I'm looking to do is to perform the Random pick from the database,
not reorder my results randomly after I've gotten all records. I don't think
the code links below help me to do that. I've been down this road already.
Thanks anyways!
Rick
On Feb 2, 2011, at 4:29 PM, Pierre
I'm not a Postgresql user but how about something like this. This assumes you
have a numeric primary key in the table you want to access.
1. Use the max() function to get the max value for the primary key of the table
2. Generate 21 random numbers between 1 and the max primary key, ensuirng
Hi Rick,
SELECT *
FROM table
WHERE condition = true
ORDER BY hashfloat8(random())
LIMIT 10;
Are you sure that the order by + limit clauses used together can't suit exactly
your needs ?
Best,
Pierre
Le 2 févr. 2011 à 23:40, Rick Harrison a écrit :
Hi Pierre,
What I'm looking to do is to
Hi Pierre,
I tried ORDER BY random() LIMIT 21
It does work the way I want it to.
It was just the way they described it work, that
didn't sound correct. It sounded like the code
would just select the first 21 records which
met my 'true' requirement in the database and
then reorder them
Hi again Pierre,
How do I avoid picking duplicates though?
Rick
On Feb 2, 2011, at 6:09 PM, Pierre Sahores wrote:
Hi Rick,
SELECT *
FROM table
WHERE condition = true
ORDER BY hashfloat8(random())
LIMIT 10;
Are you sure that the order by + limit clauses used together can't suit
Hi yet again Pierre,
Ok, I did a test where I used the limit of 48 out of 49 records.
It didn't pick even one duplicate. Hurray!!! The code must
advance the record counter randomly so no duplicates
occur. Nice, but unexpected.
Ok, all is well now.
Thanks again,
Rick
On Feb 2, 2011, at 9:41
You welcome, Rick.
PS: PostgreSQL, the amazing diamond of the ACID-SQL db world ;-)
Best,
Pierre
Le 3 févr. 2011 à 03:37, Rick Harrison a écrit :
Hi Pierre,
I tried ORDER BY random() LIMIT 21
It does work the way I want it to.
It was just the way they described it work, that
'Morning Rick,
To avoid possible border effects, did you try how it goes in adding the
distinct clause alike this :
SELECT DISTINCT *
FROM table
WHERE condition = true
ORDER BY hashfloat8(random())
LIMIT 10;
Best,
Pierre
Le 3 févr. 2011 à 04:06, Rick Harrison a écrit :
Hi yet again
10 matches
Mail list logo
