Just first of the SELECT

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

I think that will do it....

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:

Code:

---

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

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

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).]

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

Code:

---

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).]