CODEWITHSUNDEEP
sqlpostgresqlhibernatequerydsl
czetsuya
czetsuya

Reputation: 5083

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.

Upvotes: 1

Views: 132

Answers (3)

czetsuya
czetsuya

Reputation: 5083

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.

Upvotes: 2

Mykola Shchetinin
Mykola Shchetinin

Reputation: 747

Something like this. Names are dependent on your specific configuration:

SELECT DISTINCT ON(vc.id), v.vendorName, vc.categoryName
FROM vendorCategory AS vc
INNER JOIN vendors AS v ON v.categoryId = vc.id
ORDER BY random()
LIMIT 6;

so, distinct categories, random rows and only six

If you want to get a correct answer, please provide sample tables. I have just given you the idea how I see that, not the complete solution. (Because there is no real data)

Upvotes: 1

TOUZENE Mohamed Wassim
TOUZENE Mohamed Wassim

Reputation: 712

You can use DISTINCT ON () like this (special to PostgreSQL):

SELECT DISTINCT ON (vc.categoryName) v.vendorName, vc.categoryName
FROM vendorCategory vc
INNER JOIN vendors v ON v.categoryId = vc.id
LIMIT 6

Upvotes: 1

Related Questions