2

I have a groovy multi select tag () in which I want to display only the row which has MAX revision from a set of rows. Here is a post which does it in SQL: to select single row based on the max value in multiple rows?

How can I write in groovy? Should I create a named query?

Thanks in advance.

Community
  • 1
  • 1
matuda
  • 195
  • 2
  • 16

1 Answers1

0

There are various ways to achieve this. Those include namedQueries, criteria, detached criteria or even HQL. Here is the namedQuery which would get what is needed:

static namedQueries = {
    maxRevision {
        eq 'revision', {
            projections {
                max 'revision'
            }
        }
        //projections if needed
        projections {
            property 'name'
        }
    }
}

//controller
AppInfo.maxRevision().list()

With a detached criteria, it would be similar as:

AppInfo.withCriteria {
    eq 'revision', {
        projections {
            max 'revision'
        }
    }

    projections {
        property 'name'
    }
}

with HQL:

select ai from AppInfo as ai 
where ai.revision = (
    select max(revision) from AppInfo
)

taking into consideration this below domain class:

class AppInfo {
    String name
    Integer revision 
}

UPDATE
Above would give the max of all the revisions. If you are looking for max of each group then you will have to use the below HQL:

AppInfo.executeQuery("""
                      select ai from AppInfo as ai 
                      where ai.revision in (
                          select max(a.revision) from AppInfo as a 
                          where a.name = ai.name
                      )
                    """)

This can also be written as a Criteria.

dmahapatro
  • 49,365
  • 7
  • 88
  • 117
  • Thanks @dmahapatro.. I tried the named query. However, it returns only one row. I want to select all name in AppInfo with highest revision number... – matuda Mar 18 '14 at 18:26
  • Thank you.. I got a similar SQL code but its bad in performance..There is a better SQL solution by @Jonathan Leffler link below... http://stackoverflow.com/questions/8723404/how-to-select-single-row-based-on-the-max-value-in-multiple-rows/8723520#8723520 – matuda Mar 18 '14 at 19:14
  • I tried to write the last query mentioned and came to this (does`nt work, it gives out all the records). Any pointers to solve this @dmahapatro? def join = AppInfo.list { groupProperty("name") max('revision') } – matuda Mar 19 '14 at 19:25