Search code examples
sqlpostgresqlhibernatequerydsl

SQL to get 1 random record per category

For example I have 2 tables vendor and vendorCategory. And then I want to get 6 random vendors with different category. How should it be translated to postgresql? Or better QueryDSL for java.

Example I have vendors: v1, v2, v3, and so on... Then I have categories: c1, c2, c3 and so on... In our example let's say vx has a category cx. So v1 has category c1, v2 has c2 and so on...

The result of the query should return v1, v2, v3, v4, v5, v6. Or in any random order. It should not return a vendor with the same category. For example, let's say we have v1a, which has a category c1. So v1 and v1a should not be returned together.

Solution

  • Thanks to the 2 answers above I was able to come up with:

    select * from
(
SELECT DISTINCT ON(vc.id) v.vendor_name, vc.description, vc.id
FROM cat_vendor_category AS vc
INNER JOIN cat_vendor AS v ON v.vendor_category_id = vc.id
ORDER BY vc.id, random()
    ) Q
    order by random()
LIMIT 6;

    This generate a random list of vendors with unique category.

    The real problem now is how to convert it to QueryDSL or at least jpql.