1 Reply Latest reply on May 5, 2006 4:44 AM by lis0x90

    ORDER BY interferes with DISTINCT keyword

      I use dynamic EJB-QL clauses in JBoss 4.0.3 and have folowing queries:

      select distinct object(o) from orders as o, in(o.actions) as a order by o.state desc
      select distinct object(o) from orders as o, in(o.actions) as a order by a.insertTime desc, o.state desc

      In sectond query a get rowset that have duplicate results. When I see to logs:

      2006-05-03 20:17:20,578 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Order#ejbSelectFilteredOrders] Executing SQL:
      SELECT DISTINCT t0_o.OrderId, t0_o.State FROM Orders t0_o, Actions t3_a ORDER BY t0_o.State DESC
      ...
      2006-05-03 20:17:20,578 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Order#ejbSelectFilteredOrders] Executing SQL:
      SELECT DISTINCT t0_o.OrderId, t3_a.InsertTime, t0_o.State FROM Orders t0_o, Actions t3_a ORDER BY t3_a.InsertTime DESC, t0_o.State DESC

      Field t3_a.InsertTime became in select clause and causes duplicate rows in result set!

      What should I do to avoid its strange behaviour?
      Thanks for any help.