MIN Function - SQL 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MIN Function - SQL 2000

Author

Topic

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-12-01 : 15:22:16

Hi,

I have a table (test_table) with 2 records
CODE Name
296 PAT
296 Pat

I then run the following SQL against the table:
SELECT [CODE], MIN([NAME])
FROM test_table
GROUP BY [CODE]

The value returned is: 296 PAT

Even if I change the MIN function to MAX - I still get back the result PAT.

Using the MIN function I would've expected Pat to be returned.

Does anyone know why this isn't working properly?

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 15:49:45

We were just having that conversation...sort of

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43121


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(CODE int,[Name] varchar(10))
GO

INSERT INTO myTable99(CODE, [Name])
SELECT 296, 'PAT' UNION ALL
SELECT 296, 'Pat'
GO

SELECT * FROM myTable99

SELECT [CODE], MIN([NAME]COLLATE SQL_Latin1_General_Cp1253_CS_AS),MAX([NAME]COLLATE SQL_Latin1_General_Cp1253_CS_AS)
FROM myTable99
GROUP BY [CODE]
GO

SET NOCOUNT ON
DROP TABLE myTable99
GO

Brett

8-)

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-12-02 : 05:31:29

Thanks Bret,

The compatibility level of my database is 70 so the COLLATE syntax doesn't work.

I changed it to 80 to test your code and it works fine.

However, I need to keep the compatibility level at 70 for backward compatibility. The MIN function works fine on SQL Server 7.0

The database is now on SQL 2000 and it doesn't work.

Do you have any other suggestions?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-04 : 15:53:37

If your collation is set to be case insensitive then the two PAT's are equal. The MIN or MAX function could return either one with equal justification.

To solve your dilemma, you could CONVERT the string to a BINARY format and then perform the MIN/MAX.

SELECT Code, Min(convert(varbinary, Name))

HTH

=========================================
Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)
(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources