HQL query help

What other fields are in the ProductPricing table to determine which is the latest? Is there a unique ID for each record or a system entry date field? If so you could use the MAX function for them.

Something like:
String select = "SELECT p, MAX(ID) FROM  ProductPricing p where p.productId.supplierId.id=:id";

this is the table structure

id priceDate                   purchasePrice  salesPrice   consumerPrice  productID description

1  2012-11-15 00:00:00         1000          1200       1500                           1         qwqw        
2  2013-11-16 00:00:00         1600          1800       2100                           1         new price  
3  2012-10-15 00:00:00         1600          1500       1600                           2         erere  
4  2013-11-02 00:00:00         1600          1900       3400                           2         new price

I get this error when I run that query of yours

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode
 \-[AGGREGATE] AggregateNode: 'MAX'
    \-[IDENT] IdentNode: 'ID' {originalText=ID}

"SELECT top 1 p FROM  ProductPricing p where p.productId.supplierId.id=:id order by p.productId desc"

ProductPricing pp= query.getSingleResult();

this HQL is not working "top 1 " is the cause. I get error when I run the hql in eclipse

this is my complete code. from my original post I should only get back the 2nd and 4th record

String select = "SELECT p FROM  ProductPricing p where p.productId.supplierId.id=:id"; // nested condition based on the FK productpricing -> product -> supplier

    Query query = hibernateSession.createQuery(select);
    query.setParameter("id", Integer.parseInt(dsRequest.getFieldValue("supplier").toString()));

    List<ProductPricing> models = query.list();

    for (int i = 0; i < models.size(); i++)
    {
        if (models.get(i).getProductId() != null)
        {
            models.get(i).getProductId().getProductCode();
            models.get(i).getProductId().getBrandName();
            models.get(i).getPurchasePrice();
            System.out.println(models.get(i).getProductId().getBrandName());
        }

    }

Select allOpen in new window

this HQL is not working "top 1 " is the cause. I get error when I run the hql in eclipse

what is the error getting?

which DB are you using?

I am using mssql 2008

the error I get is this

2013-11-25 10:21:25,717 ERROR main org.hibernate.hql.PARSER - line 3:12: unexpected token: 1
2013-11-25 10:21:25,720 WARN main org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!
2013-11-25 10:21:25,831 ERROR main org.hibernate.hql.PARSER - line 3:12: unexpected token: 1
2013-11-25 10:21:25,831 WARN main org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!

user query.setMaxResults(1) or query.setFirstResult(1) if you are using createQUery.
if you are using nativeQuery then you can use top 1 sql statement

tring select = "SELECT p FROM  ProductPricing p where p.productId.supplierId.id=:id order by p.productId desc "; // nested condition based on the FK productpricing -> product -> supplier
Query query = hibernateSession.createQuery(select);
query.setParameter("id", Integer.parseInt(dsRequest.getFieldValue("supplier").toString()));
query.setMaxResults(1);
List<ProductPricing> models = query.list();

thanks for your comments. But this query of yours only returns one record i.e. the first record in the list. but in my case I have many products which needs to be returned with their latest purchase price. I hope you get what is my requirement

as per the records.

this is the table structure

id priceDate                   purchasePrice  salesPrice   consumerPrice  productID description

1  2012-11-15 00:00:00         1000          1200       1500                           1         qwqw        
2  2013-11-16 00:00:00         1600          1800       2100                           1         new price  
3  2012-10-15 00:00:00         1600          1500       1600                           2         erere  
4  2013-11-02 00:00:00         1600          1900       3400                           2         new price

what is the output you expect from above records?

the output I expect is the records in bold.

id priceDate                   purchasePrice  salesPrice   consumerPrice  productID description

1  2012-11-15 00:00:00         1000          1200       1500                           1         qwqw        
2  2013-11-16 00:00:00         1600          1800       2100                           1         new price  
3  2012-10-15 00:00:00         1600          1500       1600                           2         erere  
4  2013-11-02 00:00:00         1600          1900       3400                           2         new price

just to make it clearer, the sql query looks like this but in HQL I have no clue to get this to work. I need to return the client the ProductPricing object

select pp1.id, pp1.priceDate, pp1.purchasePrice, pp1.salesPrice, pp1.consumerPrice
  from product p
        inner join productPricing pp1 
    on pp1.productID = p.id
 where p.supplierID=3 
   and exists (select pp2.ProductID, MAX(pp2.priceDate) as max_priceDate 
                 from productPricing pp2 
                where pp2.productID = pp1.productID 
                group by pp2.ProductID 
               having MAX(pp2.priceDate) = pp1.priceDate) 

Select allOpen in new window

thanks for your comments ,but if you see my code which I had added yesterday.one of my requirement is I need to send the ProductPricing object back to the client.

List<ProductPricing> models = query.list();

Select allOpen in new window

I have made some changes to my HQL query,but I get error Path expected for Join. I cannot figure it out where I am making a mistake in my query

from ProductPricing pp1 join Product p where pp1.productId.supplierId.id=2 
   and exists (select pp2.productId, MAX(pp2.priceDate) as max_priceDate
    from ProductPricing pp2 where pp2.productId = pp1.productId
                group by pp2.productId  having MAX(pp2.priceDate) = pp1.priceDate)

Select allOpen in new window

ok, I solved the problem!!.thanks anyway for your comments and help. that made me think more and get to the solution.cheers