Help - Finding the newest date without using max()

[rgould]

Hi,

Can anyone help please?

select notefield, modifiedon
FROM Table1
WHERE id = '100426' and
(statusfield like '%criteria1%' OR
statusfield like '%criteria2%')

Produces a list of records based upon the criteria. I would like to be
able to only show the newest dated record, from the modifiedon field.

I've tried max(modified) on, but as I am using an aggregate function in
the query I have to use GROUP BY, which notefield does not like as this
is a ntext field.

(I get:

Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
)

Any ideas please?

[Alexander Kuznetsov]

select TOP 1 notefield, modifiedon
FROM Table1
WHERE id = '100426' and
(statusfield like '%criteria1%' OR
statusfield like '%criteria2%')
ORDER BY modifiedon DESC

-----------------------
Alexander Kuznetsov
http://sqlserver-tips.blogspot.com/