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

avatar image

blknite70
0 1 1 2

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

avatar image

SirSQL
4.9k 4 5

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

avatar image

Mister Magoo
2.1k 2 5 8

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

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

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:

x389

asked: Oct 03, 2012 at 06:07 PM

Seen: 1145 times

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

Copyright 2016 Redgate Software. Privacy Policy