Mysql rank based on column using FluentNhibernate

Question

I have following table

Student [student_id,Student_name,Marks]

I want to rank students based on marks and get specific rank position by student_id using FluentNHibernate

I wrote below hql query and got

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: A recognition error occurred. exception.

NHibernate.IQuery q = session
  .CreateQuery("SELECT student_id, student_name, marks, @curRank := @curRank + 1 AS rank"+ 
    " FROM Student p, ( SELECT @curRank := 0 ) q  " + 
    " where student_Id = '02610'  ORDER BY marks DESC")
  .SetMaxResults(1);

I think the problem is Hql cant recognize ':=' please let me know if anyone knows how to solve this problem, Thanks

Answer 1

I am not fully sure about the content of the query (in MySQL).. hope you know how to do rank, if not check an example here:

Get current rank using mysql

To use the proper (working MySQL) with NHibernate we have to adjust that like this:

NHibernate.ISQLQuery q = session
    .CreateSQLQuery("SELECT student_id, student_name...")
    .SetMaxResults(1)
    ;

The point is, the CreateSQLQuery returns ISQLQUery, which is in fact raw SQL, related to underlying DB engine dialect.

The previously used CreateQuery is about HQL, which is independent on the DB Engine dialect... but needs the Entity querying (not raw SQL)

Read more here:

17.1. Using an ISQLQuery