Just first of the SELECT

[Clunietp]

select max(fieldy) from mytable where fieldx = 'xyz'

I think that will do it....

[Tonatiuh]

It seems to funtion if I put it exactly as you put it. The number of rows of the resultset is one but, no fields are retrieved.

I tryed to make the following to set the fields to retrieve:


SELECT MAX(fieldY), field01, field02 FROM table

or

SELECT MAX(fieldY), * FROM table

But when I do this, the MAX condition don't work and I get all fields.

Any idea on how to set the fields to retrieve and just to retrieve one row, of the MAX value?

Thanks!

Tonatiuh

[Clunietp]

if you are using ADO, you can set the maxrows property to 1 and query, order by column desc. Record 1 will be your max. Also, you can do this:

Dim tmp as long
dim SQL as string
dim rs as adodb.recordset
dim cn as new adodb.connection

cn.open <connectionstringhere>

set rs = cn.execute("Select max(mynumericfield) from mytable",,adCmdText)

tmp = rs.fields(0).value

set rs = cn.execute("Select * from mytable where mynumericfield = " & tmp,,adCmdText)


I would say do it the first way so you don't have to query the database twice.

HTH

Tom

[rod]

try this:

SELECT * from MyTable Where MyTable.FieldX='xyz' and
MyTable.FieldY=(Select Max(FieldY) from MyTable)

using MAX(FieldY) from one query alone will give you only a resulting value. If you want to get all the info associated with the result of the MAX(FieldX), try the code above.

cheers,

rod

[This message has been edited by rod (edited 10-20-1999).]

[Tonatiuh]

I'm using the following SQL statament to retreive a rdoResultset and ordered DESC:

SELECT * FROM table WHERE fieldX='xyz' ORDER BY fieldY DESC

I ordered by 'fieldY' DESC because I just need one row, the row which has the mayor value of 'fieldY'.

Is there any way to retrieve just this row which has the mayor value of fieldY?
I want to have a resultset with just one row.

Any idea will be deeply apreciated.

Thanks!

Tonatiuh


[This message has been edited by Tonatiuh (edited 10-20-1999).]