Delete all rows except 1 the most recent

Question

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

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard - stop using it — marc_s, Nov 26 '12 at 05:58

Ryan Weir · Accepted Answer · 2012-11-26T06:52:23.687

4

~~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.

edited Nov 26 '12 at 06:52

answered Nov 26 '12 at 05:34

Ryan Weir

6,377
5
40
60

Tried, no luck. There was an error parsing the query. [ Token line number = 1,Token line offset = 23,Token in error = S1 ] – Didar_Uranov Nov 26 '12 at 05:37
query in update doesnt execute. think TOP is nut supported in CE – Didar_Uranov Nov 26 '12 at 06:47
Looks like it is in CE 3.5 http://technet.microsoft.com/en-us/library/bb686896.aspx, but the syntax requires brackets e.g. 'TOP (1)' instead of 'TOP 1'. Try that – Ryan Weir Nov 26 '12 at 06:51
Thanks that works, but not exactly the same. Correct: DELETE FROM StateLogs WHERE NodeId = {0} AND Id NOT IN (SELECT TOP (1) Id FROM StateLogs WHERE NodeId = {0} ORDER BY Modified DESC) – Didar_Uranov Nov 26 '12 at 07:26

John Woo · Answer 2 · 2012-11-26T05:44:28.073

0

try something like this,

DELETE  a
FROM    [StateLogs] a
        LEFT JOIN
        (   
            SELECT NodeID, MAX([Modified]) maxDate
            FROM [StateLogs]
            GROUP BY NodeID
        ) b ON a.nodeID = b.NodeID AND
                a.[Modified] = b.maxDate
WHERE   b.NodeID IS NULL

UPDATE 1

DELETE  
FROM    [StateLogs] 
WHERE   NodeID NOT IN
        (
            SELECT NodeID
            FROM
                    (   
                        SELECT NodeID, MAX([Modified]) maxDate
                        FROM [StateLogs]
                        GROUP BY NodeID
                    ) s
        )

edited Nov 26 '12 at 05:44

answered Nov 26 '12 at 05:33

John Woo

258,903
69
498
492

No luck. There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = FROM ] – Didar_Uranov Nov 26 '12 at 05:41

Delete all rows except 1 the most recent

2 Answers2