x

trying to find max value of a column

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.
more ▼

asked Oct 03, 2012 at 06:07 PM in Default

blknite70 gravatar image

blknite70
0 1 1 1

please post the code you are using and we can help you better
Oct 03, 2012 at 06:33 PM Kev Riley ♦♦

some sample data might be useful, too...

...particularly when supplied with the result you require...
Oct 03, 2012 at 07:57 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
SELECT price1, itemid 
from YourTable  
where price1 = ( 
                SELECT MAX(price1)  
                from YourTable);
more ▼

answered Oct 03, 2012 at 09:30 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

Perfect, that's what i was looking for SirSQL....thank you!
Oct 04, 2012 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, 2012 at 10:12 AM Mister Magoo
(comments are locked)
10|1200 characters needed characters left
SELECT TOP 1 itemid,price1 
FROM YourTable
ORDER BY price1 DESC
more ▼

answered Oct 03, 2012 at 10:35 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

good to see you back MM
Oct 04, 2012 at 05:47 AM Scot Hauder
Thanks Scot, not really been away, just busy :)
Oct 06, 2012 at 03:38 PM Mister Magoo
(comments are locked)
10|1200 characters needed characters left
SELECT max(price1), itemid from YourTable
more ▼

answered Oct 08, 2012 at 09:55 AM

ksenthilkumar_dba gravatar image

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, 2012 at 10:15 AM Mister Magoo
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x279

asked: Oct 03, 2012 at 06:07 PM

Seen: 860 times

Last Updated: Oct 08, 2012 at 10:15 AM