0

How do I write a query to fetch records which is distinct in one column and desc order in another column.

I was able to fetch the records using the following statement.

  select * from
 (select * from t1 
  where id=14 
  order by ttimestamp desc) as h 
  group by hnumber 
  order by ttimestamp desc

But when I tried the same in JPA. 

  SELECT m from 
  (SELECT m from t1 m 
   WHERE m.user = :user 
   ORDER BY m.tTimestamp DESC) as h 
  GROUP BY m.hNumber 
  ORDER BY m.tTimestamp DESC

I got an error saying syntax error.

  Exception Description: Syntax error parsing [SELECT m from (SELECT m from t1 m WHERE m.user = :user ORDER BY m.tTimestamp DESC) as h GROUP BY m.hNumber ORDER BY m.tTimestamp DESC]. 
   The right parenthesis is missing from the sub-expression.
   An identification variable must be provided for a range variable declaration.
   The query contains a malformed ending.

Any help is appreciated.

User12111111
  • 1,179
  • 1
  • 19
  • 41
  • @Leo I tried this as well.. SELECT m from (SELECT n from t1 n WHERE n.user = :user ORDER BY n.tTimestamp DESC) as h GROUP BY m.hNumber ORDER BY m.tTimestamp DESC and it did not work. – User12111111 Apr 01 '14 at 13:27
  • if your subquery is h, why are you selecting m? – Leo Apr 01 '14 at 14:41
  • @Leo Could you please suggest me the correct way of implementing this query. I tried this "SELECT m from (SELECT n from t1 n WHERE n.user = :user ORDER BY n.tTimestamp DESC) m GROUP BY m.hNumber ORDER BY m.tTimestamp DESC" but failed. – User12111111 Apr 02 '14 at 08:03
  • TBH I don't understand why you need to do a subselect in your statement, the exterior GROUP BY invalidates the inner ORDER BY. Do you want to retrieve the latest entry for each ID or for a specific ID? – Tassos Bassoukos Apr 02 '14 at 11:03
  • @TassosBassoukos Yes, I want to retrieve records for each ID which has max timestamp from t1 table. – User12111111 Apr 03 '14 at 06:27
  • See if [this answer](http://stackoverflow.com/questions/6296347/jpa-select-latest-instance-for-each-item) works for you. – Tassos Bassoukos Apr 03 '14 at 10:07

0 Answers0