Get only the newest 5 items

[Hussein Baghdadi]

Hi.
I want to get from the database only the newest 5 books.
My first apprach was to get all the books and then use subList( ) method.
It is abvious not a performant way.
(I'm using Hibernate3.2.2)

[Mark Spritzler]

I think it comes down to the database, and not Hibernate. And getting the newest 5 is not a common query directly with a databse. Meaning without you building it into you model.

So as an example, if you had a Date/Time field that was date/time inserted, then you could do a query to get back 5 record, and it is ordered in reverse chronological order by that date/time field.

Mark

[Hussein Baghdadi]

My Book table has RELEASING_DATE column.
But actually I don't know how to get a specific number of rows (sorry, I'm not SQL pro). :roll:

[Peer Reynders]

Because TOP 5 is not a relational concept it isn't easily expressed in terms of standard (portable) SQL. We need to know what database you are using so that that we can use the appropriate extension. For example, Oracle:

SELECT
    title, isbn
FROM
    (SELECT title, isbn FROM books ORDER BY release_date DESC)
WHERE
    ROWNUM < 6;

MS T-SQL:

   SELECT TOP 5
       title, isbn
   FROM books
   ORDER BY release_date DESC;

MySql (I'm guessing a bit here):

   SELECT
       title, isbn
   FROM books
   ORDER BY release_date DESC
   LIMIT 5;

[ April 23, 2007: Message edited by: Peer Reynders ]

[Hussein Baghdadi]

Since I'm using Hibernate, Can I employ HQL ?
(BTW, I'm using MySQL4.X);

[saranga rao]

hi,,

use setMaxResults(int maxResults) in session.createCriteria().setFirstResult(int firstResult)