How to get a distinct result, but return another column in the select?

Question

I want to get a distinct result based on a property, but return the id in the select because I will be using it in a subquery.

e.g.

(List<Article>) session.createQuery("from Article a where a.id in (select distinct a2.title from article a2 where a2.body = :body");
setString("body", "")
.list();

The key section is the subquery, I want to return the id, not the a2.title property. Can this be done?

(the table Article has duplicates, so I just want to return any one of them it doesn't matter as long as the body = "").

Answer 1

what SQL server is this?

Instead of using distinct you could ask for just one line to return, and then you'd only need to select the field(s) you actually need.

In PL\SQL (Oracle) you can use rownum:

where rownum = 1

In other SQL you could use limit:

Limit 1

read: http://www.petefreitag.com/item/451.cfm

Answer 2

If you don't care which row is returned, you could change your subquery to:

select MIN(a2.id) from article a2 where a2.body = :body

This would return the lowest id from article where body matches.