Take MAX string to roll up multi records in one

[lyudmila]

Hi,
I have a very silly question

If I have three records
vndnumber vendorname
---------- --------------
296333 Kraft Inc.
296333 Kraft Inc
296333 Kraft Inter.
and I need to group by vndnumber and make one record from these three and I was told to take MAX(vendorname), why my result is:

296333 Kraft Inc.
and not:
296333 Kraft Inter.

Both fields are varchar and script I use:

SELECT DISTINCT
vndnumber,
vndnumber=MAX(vendorname)
INTO DBO.P_VENDOR
FROM DBO.T_VENDOR
GROUP BY vndnumber

 

[SQLBill]

This will never work:

vndnumber=MAX(vendorname)

vndnumber is not the same as vendorname.

Try this:

SELECT DISTINCT 
    vndnumber,
    vendorname
    INTO DBO.P_VENDOR
    FROM DBO.T_VENDOR
    WHERE vendorname = (SELECT MAX(vendorname)
                        FROM DBO.T_Vendor)
    GROUP BY vndnumber

This will group your values by vndnumber, then within that group it will find the max vendorname. Using that max vendorname it will then retrieve the correct data. (Hopefully)

-SQLBill

 

[lyudmila]

I have messed up the script I'm using
SELECT DISTINCT
vndnumber,
vendorname=MAX(vendorname)
INTO DBO.P_VENDOR
FROM DBO.T_VENDOR
GROUP BY vndnumber
Example:
vndnumber vendorname STRING LENGTH
------- ------------------------------------ -------------
222222 MMMMMMM MMMMM INC. 18
222222 MMMMMMM-MMMMM INC. - AJAX 25
222222 MMMMMMM-MMMMM INC. -AJAX 24
222222 XXXXXXXXXXXXXXXXXXXXXXXXX 25

Result:

vndnumber vendorname
------- ------------------------------------
216360 XXXXXXXXXXXXXXXXXXXXXXXXX

My guess it has something to do with collation on server, it selects not MAX string length, but uses also alphabetical order for letters? Any idea?

 

[ESquared]

Max is about value... value of a character field has to do with alphabetical order, just like value of a numeric field has to do with numeric order.

You want the expression

Max(Len(vendorname))

 

[SQLBill]

AAAHHHH, this changes things:

My guess it has something to do with collation on server, it selects not MAX string length, but uses also alphabetical order for letters? Any idea?

You had/have a misunderstanding about MAX. As ESquared pointed out you really needed LEN. LEN returns the length of a value. MAX returns the maximum value from the column (and it's based on the alphabetical/numerical order).

Check out MAX and LEN in the BOL.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875