How to determine a column's "largest" value?

jkj2001 · Post by **jkj2001** » Thu Nov 04, 2004 7:09 pm

Hi all,

We're using version 4.04.1067366033 of texis here, and I was wondering how I could go about determining the largest value sitting in a given column?

For instance, say I have an Author field; I'd like to know who has the longest name and how many characters it is. Thanks!

Post by **John** » Thu Nov 04, 2004 10:38 pm

select max(length(Author)) from Table

will give you the length of the longest Author. That doesn't give you the name of that Author. You might also want:

select Author, length(Author) from Table order by 2 desc

will list all the authors by length of name, so if you just pluck off the first value you'll have a name.

jkj2001 · Post by **jkj2001** » Fri Nov 05, 2004 1:56 pm

Thanks John!

jkj2001 · Post by **jkj2001** » Fri Nov 12, 2004 2:13 pm

Hi again,

This function works great...except when I tried it on a blob field. Is there a similar method I can use to find the max length in that column?

Post by **mark** » Fri Nov 12, 2004 2:50 pm

Try converting it to varchar first convert(Author,'varchar')

jkj2001 · Post by **jkj2001** » Fri Nov 12, 2004 4:01 pm

Thanks-- I got what I needed by running a select and ordering by length, in descending sequence.

For some reason though, when I tried using max --as in "max(length(convert(myblob,'varchar')))"-- I received an "ABEND: signal 11" error? Not sure why, it cropped up immediately after the tsql command.

Post by **mark** » Fri Nov 12, 2004 5:57 pm

ABEND means texis had problems handling that query. What version of texis?