Hibernate SQL findin most often appearing number?

Question

I have a joint table of user id and test that they took id. How can I write Hibernate query that shows which test was taken most often?

In this case I just need to get number 1 as a result:

user_id     test_id
23          1
24          1
25          1
53          32
76          32 
77          1 
78          1

You can use a groupby criteria -> https://stackoverflow.com/questions/8491796/hibernate-group-by-criteria-object and then count by it. — panoskarajohn, Jan 05 '20 at 17:33

score 1 · Answer 1 · answered Jan 05 '20 at 17:35

1

try the below HQL Query

String HQL = "
SELECT       test_id
    FROM     `your_table`
    GROUP BY test_id
    ORDER BY COUNT(*) DESC
    LIMIT    1";

answered Jan 05 '20 at 17:35

dassum

4,727
2
25
38

Thank you, it worked very well in PostgreSQL, although Hibernate didn't understand the limit part. I just took 1st result from the list. – Agne Jan 05 '20 at 18:44

score -1 · Answer 2 · answered Jan 05 '20 at 17:23

-1

Try to select the max value:

String HQL = "select max(test_id) from my_entity";
Query query = sess.createQuery(HQL);
List list = query.list();
System.out.println("Max is " + list.get(0));

answered Jan 05 '20 at 17:23

Beppe C

11,256
2
19
41

2

Max value gives the biggest number, in this case 32 – Agne Jan 05 '20 at 17:28

Hibernate SQL findin most often appearing number?

2 Answers2