1

I have a table named "cities" which have thousands of records with only two fields namely city and state. Though the type and length of the both fields defined as varchar(100) in my table, I am sure no values in the columns took up 100 characters. I want to write a query to know which value of the state column takes the heighest numbers of characters. I tried with:

select distinct max(state) from cities;

I know this is not the query what I want. Is there any SQL function to accomplish this task?

Parveez Ahmed
  • 1,325
  • 4
  • 17
  • 28

3 Answers3

0

You don’t need distinct; there is only one maximum length.

To return the state(s) with the longest value:

select state
from cities
where length(state) = (select max(length(state)) from cities)

——-

These queries might be more helpful...

To return all states and their length, whose length is longer than a certain length (eg 10):

select state, length(state)
from cities
where length(state) > 10
order by 2 desc

To return the longest 10 state names and their length:

select state, length(state)
from cities
order by 2 desc
limit 10
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Sir, in all your queries above, what does 2 actually mean in "order by 2" phrase? – Parveez Ahmed Jul 22 '18 at 04:40
  • The third query doesn't quite do that , but I admit we're splitting hairs – Strawberry Jul 22 '18 at 07:41
  • Also, you're assuming (not unreasonably) that state names are composed of non-multibyte characters. – Strawberry Jul 22 '18 at 07:44
  • @rosemary `order by 2` means *order by selected column number 2*. It’s standard SQL, so it’s portable, and is simpler especially when the column is an expression, which you then don’t have to maintain in both the select and order by clauses. – Bohemian Jul 22 '18 at 15:17
0

Another option would be to use ORDER BY with LIMIT:

SELECT state, CHAR_LENGTH(state) AS length
FROM cities
ORDER BY CHAR_LENGTH(state) DESC
LIMIT 1;

If there could be ties and you also want those ties, then we can try a different approach.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
-1

This question is Duplicate But i will answer, you should use length() function with the max() function which i got here :-

select max(length(state)) from cities;
Azhy
  • 704
  • 3
  • 16