0

I'm having difficulty representing this query which I write for select max month and max year rows from given 'uid'. I have 6 data in which 3 data has uid '4' and other 3 data has uid '5'. I want to get the max month and max year data from both uid 4 and 5. Here is my criteria query I get the this result.

Size of result list: 2
uID: 5 nID: cdf Month: 11 Year: 2012
uID: 4 nID: f58 Month: 10 Year: 2012

But when i change year of uid from 2012 to 2013 it only returns uid 4's row only.

DetachedCriteria maxYearQuery = DetachedCriteria.forClass(Demo.class);
        ProjectionList yearProj = Projections.projectionList();
        yearProj.add(Projections.max("contextYear"));
        yearProj.add(Projections.max("contextMonth"));
        yearProj.add(Projections.groupProperty("entityUUID"));
        maxYearQuery.setProjection(yearProj);

    Criteria crit = session.createCriteria(Demo.class);
            crit.add(Subqueries.propertiesIn(new String[] {"contextYear","contextMonth", "entityUUID"}, maxYearQuery));
            crit.addOrder(Order.desc("entityUUID"));

my table is looks like this..

>

uID: 4 nID: cdf Month: 10 Year: 2012

uID: 4 nID: f58 Month: 9 Year: 2012

uID: 5 nID: f58 Month: 8 Year: 2012

uID: 5 nID: f58 Month: 11 Year: 2012

uID: 5 nID: f58 Month: 10 Year: 2013

uID: 4 nID: f58 Month: 9 Year: 2012

I want the 1st and 5th record from my database. I am stuck at this stage. Please give some guidance to get record by grouping.

Thanks in advance.

Jimmy
  • 1,719
  • 3
  • 21
  • 33

2 Answers2

0

You want this type of query

Select Max(month),Max(Year) from Demo group by uid;

Dheeraj
  • 25
  • 6
  • Thanks Dheeraj and Yes , I want same type of query. The above query is work well but for same year. When I change year it returns only one result. – Jimmy Feb 08 '15 at 17:53
  • It should return whole object not only max month and year. – Jimmy Feb 08 '15 at 19:18
0

i find solution of your problem

Supose name of table is Demo and in which three column uid,month ,year

Query:

select a.uid,a.year,max(month) from Demo a inner join(select uid,max(year) m from Demo group by 1) d on a.uid=d.uid and a.year=d.m group by 2;

It will work fine check it and give me feedback

Dheeraj
  • 25
  • 6