retrieve max(col_name) and row's id

D

dn

All: I wish to retrieve the maximum value in a price column AND that row's
id: I'm stuck, please break the impasse.

Many TIA's...
 
L

lance

Try this

select top 1 rowid,max(price) from yourtable
group by rowid
order by max(price) desc

hth,
Lance
 
J

John Spencer (MVP)

SELECT IDFIELD, PriceField
FROM Table
WHERE Table.PriceField =
(SELECT Max(T.PriceField) as MaxPrice
FROM Table as T)

Put your fieldnames and table name in place of the sample names I used.
 
T

Tom Ellison

Dear dn:

To retrieve the maximum Price value, a simple aggregate query is all that is
needed:

SELECT MAX(Price) FROM YourTable

However, in order to get any other column values from the table, you use
this as a subquery in a criteria:

SELECT id, Price
FROM YourTable
WHERE Price =
(SELECT MAX(Price) FROM YourTable)

Note that, if two or more rows have this highest price value, they will all
be returned.
 
Top