[ic] is there a way to pull out a max value in a database

[interchange-users@icdevgroup.org (Stefan Hornburg)]

On Wed, 20 Nov 2002 13:02:41 -0800
paul jordan <paul@gishnetwork.com> wrote:

> > > > Tom asked...
> > > > >[loop search=|
> > > > >       fi=my_database
> > > > >       rf=code
> > > > >       sf=code
> > > > >       se=maximum
> > > > >  |]
> > > > >[loop-code]
> > > > >[/loop]
> > > > >
> > > > >I want the maxium value to be displayed.
> > > > >If the code field is from 0-100. it will only display
> > > > >100
> > > > >There is probably a simple way to do this but my brain
> > > > >is not functioning properly right now. 8-)
> > > > >
> > > > >Thanks for any help,
> > > > >Tom
> > > > How about just setting the rows returned to 1? Set ml=1 in your
> > > > search.
> > > >
> > > > John.
> > >
> > >
> > > Yes, maybe ml=1, but to get the last record only... maybe try:
> > >
> > > to=r,n
> > > tf=sku
> > >
> > > untested with the ml=1 command, but the idea is to sort on sku, and
> > > reverse the order, and return one value.
> > >
> > > If that doesn't work, try it without the ml=1, and kill the
> > loop after
> > > the first iteration.
> > >
> > > If you are using a mysql database, I believe mysql has some function
> > > built in for this.
> >
> > I suppose that [query sql="select code from my_database order
> > by code desc limit 1"]
> > will work on any database in IC.
> >
> > Bye
> >         Racke
> 
> 
> OH ok, yes IC, this works great Racke. Tom, I have tested this here:
> 
> http://demo.icdevgroup.org/i/demo1/test
> 
> using:
> 
> [query type=list sql="select sku from products order by sku desc limit
> 1"]
> [list]
> [sql-code]<br>
> [/list]
> [/query]
> 
> This is interesting. Thanks Racke!

It works due to the fact that IC passes the query to the SQL::Statement module
if the database isn't a SQL one. 

select sku from products order by sku desc limit 1 

is equivalent to

select max(sku) from products

Bye
        Racke