Selecting the highest value

Hi,

this must be quite simple, but I have no clue how to do it: Can I select th=
e row where a certain column has the highest table of all rows satisfying a=
nother where condition?

Example:

my ($last_pic_id) =3D $dbh->selectrow_array("SELECT picture_id FROM picture=
s WHERE gallery_id =3D $self->{fields}->{gallery_id}->{value} AND position =
=3D XXXX");

I need the picture being at the last position within the gallery. Can I do =
that? I tried a workaround using the rowcount (via COUNT(*)), but that is a=
bit unstable in case the position list has gaps.

Thanks,

Jan
--=20
Any sufficiently advanced technology is insufficiently documented.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Selecting the highest value

On 7/28/05, Jan Eden wrote:

> this must be quite simple, but I have no clue how to do it: Can I select =
the row where a certain column has the highest table of all rows satisfying=
another where condition?

position =3D MAX(position)

might do the trick. Dunno, which version of MySQL supports it, but the
latest should do.



--=20
What are the first steps on the moon, compared to your child's?

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Selecting the highest value

Jochen Wiedmann wrote on 28.07.2005:

>On 7/28/05, Jan Eden wrote:
>
>> this must be quite simple, but I have no clue how to do it: Can I select=
the=20
>row where a certain column has the highest table of all rows satisfying an=
other=20
>where condition?
>
>position =3D MAX(position)
>
>might do the trick. Dunno, which version of MySQL supports it, but the
>latest should do.
>
I feared that it would be that simple. Doh!

BTW, the manual says you need a subselect as of MySQL 4.1

SELECT id FROM table WHERE position =3D (SELECT MAX(position) FROM table);

In older version, you first need to do a select for the MAX value, then a s=
econd one for the row.

Thanks,

Jan
--=20
Any sufficiently advanced technology is indistinguishable from a Perl scrip=
t. - Programming Perl

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org