Equivalent of ROW_NUMBER() function in hibernate

Question

How to express the following SQL in hibernate:

SELECT *, ROW_NUMBER() OVER (ORDER BY SampledDate DESC) AS Row
FROM Sample
WHERE AssetId = @AssetId

Answer 1

As suggested here you don't. You create a view with the ROW_NUMBER() inside.

And be aware that if you have JOINs that "multiply" your rows, then your ROW_NUMBER() will be nearly useless unless you use the PARTITION BY