getting the right value using MAX() on SQL

Question

looking TO GET THE MAX() VALUE from a table like the one below. Here is what the table looks like

LEVEL  ACCOUNTNAME
0.11      namea
1.25      namea
14.55     namea
3.52      namea

HER IS THE CODE THAT I USE:

SELECT 
MAX(LEVEL) AS HIGHESTLEVEL, ACCOUNTNAME
FROM TABLE
GROUP BY ACCOUNTNAME

however my returned answer is

LEVEL    ACCOUNTNAME
3.52        namea

I should be getting 14.55 not 3.52. anyone know what is going on?

Which dbms??? It's the most important thing when asking for question. You don't want to get answer, but useless because it is for the wrong dbms. Like the answer below, it will work if you happen to use SQL Server. But it will not work with other dbms. — Eric
– Eric, Commented Dec 13, 2017 at 17:55

Gareth Lyons · Accepted Answer · 2017-12-13 18:06:39Z

3

Your Level field is a string. Try this in SQL 2012+:

SELECT MAX(TRY_CONVERT(FLOAT, Level)) AS HIGHESTLEVEL, ACCOUNTNAME
FROM TABLE
GROUP BY ACCOUNTNAME

This will return NULL for any ACCOUNTNAMEs that don't have any valid numeric values for a given value of LEVEL.

edited Dec 13, 2017 at 18:06

answered Dec 13, 2017 at 17:47

Gareth Lyons

1,97214 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

WWaldo Over a year ago

Do you know if there is a way to get around the error? it seems that the column may have values that can't be converter to numeric value.

Gareth Lyons Over a year ago

What RDBMS are you using? The method could vary.

WWaldo Over a year ago

I'm using SQL Server

Eric Over a year ago

@OzzyBenitez Then add SQL Server tag to your question.

WWaldo Over a year ago

Thank you @GarethLyons, unfortunately it did not work i got this error:

|

Zorkolot · Accepted Answer · 2017-12-14 21:28:49Z

1

Here's one way using TOP and DESC:

SELECT TOP 1 *
  FROM TABLE
ORDER BY CAST([Level] as decimal(16,2)) DESC

Another way using MAX and a group by:

SELECT MAX(CAST([Level] as decimal(16,2))) AS [Level]
      ,AccountName
  FROM TABLE
GROUP BY AccountName

Alternatively, you could change the datatype of [Level] to a decimal or float (which it probably should be, instead of varchar) and your original query would work.

answered Dec 14, 2017 at 21:28

Zorkolot

2,0271 gold badge12 silver badges8 bronze badges

Collectives™ on Stack Overflow

getting the right value using MAX() on SQL

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related