sql - How to use SELECT DISTINCT with RANDOM() function in PostgreSQL? -


I'm trying to run a SQL query to get four random objects. As there is more than one towb in product_filter , I use DISTINCT in SELECT , so I Receive this error:

For the difference, the expression should appear in the expression selection list

But if I RANDOM () In my SELECT the result will be zero (0) DISTINCT .

How to use someone DISTINCT random () function? Below is my problematic query. 

  SELECT DISTINCT p.id, p.ditle FROM product_filter pf at JOIN product p pf.cod_product = p.cod add filter f pf.cod_filter = f.cod WHERE p.visible = TRUE LIMIT 4 Order by Random;    
 
 
 You can make your query easier to avoid the problem:   SELECT p.cod, p.title product p WHERE p.visible and EXISTS (SELECT 1 from product_filter pf JOIN filter f fcod = pf.cod_filter at pF.cod_product = p.cod) () LIMIT 4;   
 Key points:  
     
  •  
     You have only columns in the result from the table  product , other tables only checked For the existence of a matching line, this is probably the fastest and easiest solution for a case like this  . Using it does not multiply the rows from the base table  product , so you do not need to delete them again with the  DISTINCT .  
  •    
  •  
      LIMIT  to  ORDER BY   
  •   
  •  
     < P> 
     
     = 't'  to  p.visible , because it  should  be a boolean column.

Comments

Post a Comment