trying to find max value of a column

Question

trying to find max value of a column name 'price1' and its itemid but its generating lots of values in column itemid. I am just looking for one maximum value with its itemid. Thanks for help.

Answer 1

0

SELECT price1, itemid 
from YourTable  
where price1 = ( 
                SELECT MAX(price1)  
                from YourTable);

answered Oct 03 '12 at 09:30 PM

SirSQL
4.6k ● 1 ● 3

Perfect, that's what i was looking for SirSQL....thank you!

Oct 04 '12 at 08:27 PM blknite70

I would just like to point out that this is an incorrect answer to the OP question, which states that one result is required.

If there are multiple entries with the same price, this will return multiple rows for that price (as long as it is the maximum price).

As far as performance is concerned, this is also a bad choice as it will perform two table scans (unless you are very odd and have indexing on the price1 column).

Oct 08 '12 at 10:12 AM Mister Magoo

(comments are locked)

Answer 2

0

SELECT TOP 1 itemid,price1 
FROM YourTable
ORDER BY price1 DESC

answered Oct 03 '12 at 10:35 PM

Mister Magoo
440 ● 3

good to see you back MM

Oct 04 '12 at 05:47 AM Scot Hauder

Thanks Scot, not really been away, just busy :)

Oct 06 '12 at 03:38 PM Mister Magoo

(comments are locked)

Answer 3

0

SELECT max(price1), itemid from YourTable

answered Oct 08 '12 at 09:55 AM

ksenthilkumar_dba
-7

This is just not a valid SQL statement. Where is your GROUP BY? And even with the GROUP BY, it would return one row per ItemId.

Oct 08 '12 at 10:15 AM Mister Magoo

(comments are locked)

trying to find max value of a column

Follow this question

Related Questions