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

Post Reply
jkj2001
Posts: 142
Joined: Fri Mar 29, 2002 1:39 pm

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

Post by jkj2001 »

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!
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

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

Post by John »

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.
John Turnbull
Thunderstone Software
jkj2001
Posts: 142
Joined: Fri Mar 29, 2002 1:39 pm

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

Post by jkj2001 »

Thanks John!
jkj2001
Posts: 142
Joined: Fri Mar 29, 2002 1:39 pm

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

Post by jkj2001 »

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?
User avatar
mark
Site Admin
Posts: 5519
Joined: Tue Apr 25, 2000 6:56 pm

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

Post by mark »

Try converting it to varchar first convert(Author,'varchar')
jkj2001
Posts: 142
Joined: Fri Mar 29, 2002 1:39 pm

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

Post by jkj2001 »

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.
User avatar
mark
Site Admin
Posts: 5519
Joined: Tue Apr 25, 2000 6:56 pm

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

Post by mark »

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