2

I am not able to make one clean SELECT

I have postgresql table with fields id , providerid, deal_name

I want to make random select with unique providerid 

SELECT * FROM deals ORDER BY random() LIMIT 10

How I can set this to return 10 results with unique providerid's ?

Svetoslav
  • 4,686
  • 2
  • 28
  • 43
  • Similar questions: http://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql, http://stackoverflow.com/questions/6090969/postgresql-select-20-random-records, http://stackoverflow.com/questions/5297396/quick-random-row-selection-in-postgres – Craig Ringer Aug 17 '12 at 14:57
  • 1
    What exactly do you mean by "unique" here? Distinct, ie 10 that are not the same? Please show your table structure and a little sample data. – Craig Ringer Aug 17 '12 at 14:59

2 Answers2

3

Can your question be rephrased as:

"For each of ten randomly selected providers, find one randomly selected deal offered by that provider" ?

If so, that pretty much tells you what to do. You need two layers, one pass to randomly select ten providers, then one pass to select one random deal per provider.

Given dummy data:

create table spam ( deal text, provider text );

insert into spam(deal,provider) 
SELECT prov_id||n::text, prov_id FROM (
    SELECT chr(x) AS prov_id from  generate_series(97, 92+26) x) y 
CROSS JOIN generate_series(1,10) n;

select provider FROM spam GROUP BY provider ORDER BY random() LIMIT 10;

Here's one that works:

SELECT
 (SELECT deal FROM spam WHERE spam.provider = sel_prov.provider ORDER BY random() LIMIT 1),
 sel_prov.provider
FROM (select provider FROM spam GROUP BY provider ORDER BY random() LIMIT 10) sel_prov;

... pretty much just by implementing the phrasing above as SQL. Don't know if it's fast; you get the idea.

If the above rephrasing isn't correct, please clarify your question with sample data and an example, or some more detail in your description.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
2

If the rephrasing proposed by @Craig Ringer is correct:

For each of ten randomly selected providers, find one randomly selected deal offered by that provider

then I can suggest another solution:

SELECT d.id, d.providerid, d.deal_name FROM (SELECT DISTINCT ON (providerid) providerid , id, deal_name FROM deals) d ORDER BY random() LIMIT 10;

I hope it will help!

aymeric
  • 3,877
  • 2
  • 28
  • 42