Delete all rows except 1 the most recent

Want to delete all rows from table except 1 that is the most recent.

This query works fine with SQL Server 2008 Standard edition:

DELETE S1 
FROM StateLogs S1, StateLogs S2 
WHERE S1.NodeId = S2.NodeId AND S1.NodeId = {0} AND S1.Modified < S2.Modified

But fails on SQL Server Compact Edition 3.5

There was an error parsing the query.
[ Token line number = 1, Token line offset = 11,Token in error = FROM ]

What is the equivalent for Compact edition?

EDIT

Table design http://pastebin.com/Akwpypkm

Your delete statement appears to have a syntax error, try changing the query from

DELETE S1 FROM ...

to

DELETE FROM ...

UPDATE:

Try this statement as a replacement for yours:

DELETE FROM StateLogs WHERE Id NOT IN (SELECT TOP (1) Id FROM StateLogs ORDER BY Modified DESC)

Also just a heads up that you may have problems with insert/update operations against the table based on the schema you've provided because your primary key is larger than the maximum: http://msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx

This could pretty cause mysterious runtime failures in your application.