I know that this bug report is old. However, because more people might arrive here looking for a solution to this problem, I want to comment about the workaround that I've been using for all these years, since the first time that I had to deal with the impossibility to use DISTINCT ON.
It consists in using a SUBSELECT in the WHERE clause that chooses the first matching row only, taking advantage of the function MIN (or MAX if you prefer). It's more complicated to write and requires to have a single primary key in the table. But it works well and can be used even with aggregate functions such as COUNT.
For example, if I follow the example of the reporter, I would do this:
SELECT tbl.F1, tbl.F2
FROM tbl
WHERE tbl.ID =
(SELECT MIN (tbl2.ID)
FROM tbl AS tbl2
WHERE tbl2.F1 = tbl.F1)
To improve the efficiency, it's recommended to create an index for the column F1.
I know that you can use the GROUP BY clause to solve this problem. However, if I needed to count the resulting rows, I couldn't do it in a single query. Because the function COUNT would count ALL the registries, before grouping them. Hibernate doesn't support nested queries in the FROM clause either, so one can't use the typical approach of many databases:
SELECT COUNT (*)
FROM (
SELECT DISTINCT tbl.F1, tbl.F2
FROM tbl)
So, how would I do that using my approach? This way:
SELECT COUNT (*)
FROM tbl
WHERE tbl.ID =
(SELECT MIN (tbl2.ID)
FROM tbl AS tbl2
WHERE tbl2.F1 = tbl.F1)
I hope that this helps more people.
I believe that DISTINCT ON is not supported in other databases, so I don't believe that Hibernate supports this ever. But it would be great if Hibernate implemented subqueries in the FROM clause. This would give us much more flexibility.
|
