retrieve max(col_name) and row's id

[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...

 

[lance]

Try this

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

hth,
Lance

 

[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.

 

[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.