select max(fieldy) from mytable where fieldx = 'xyz'
I think that will do it....
Printable View
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:
But when I do this, the MAX condition don't work and I get all fields.Code:SELECT MAX(fieldY), field01, field02 FROM table
or
SELECT MAX(fieldY), * FROM table
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:
I ordered by 'fieldY' DESC because I just need one row, the row which has the mayor value of 'fieldY'.Code:SELECT * FROM table WHERE fieldX='xyz' ORDER BY fieldY DESC
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).]