Hi, I think this is fixed in the trunk. Could you check the latest nightly build? See http://h2database.com/html/build.html#automated
Regards, Thomas On Thu, Dec 3, 2015 at 3:25 PM, Dave Anonymous <[email protected]> wrote: > i have a table like that: > CREATE TABLE ALARM_HISTORY( > ID INT AUTO_INCREMENT NOT NULL, > ACCOUNT INTEGER NOT NULL, > ALARMDATE TIMESTAMP > ); > ALTER TABLE PUBLIC.ALARM_HISTORY ADD CONSTRAINT PUBLIC.PK_ALARM_HISTORY > PRIMARY KEY(ID); > CREATE INDEX PUBLIC.IDX_ALARM_HISTORY_ALARMDATE ON > PUBLIC.ALARM_HISTORY(ALARMDATE); > CREATE INDEX PUBLIC.IDX_ALARM_HISTORY_ACCOUNTALARMDATE ON > PUBLIC.ALARM_HISTORY(ACCOUNT, ALARMDATE); > > > Account is a foreign key. > > Problem: > This is working (result is 500.000): > SELECT ID FROM ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE ASC LIMIT > 500000; > SELECT * FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID IN (SELECT ID FROM > ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ID ASC LIMIT 500000); > > This is not working (result is 0) > SELECT * FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID IN (SELECT ID FROM > ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE ASC LIMIT 500000); > or > DELETE FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID IN (SELECT ID FROM > ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE ASC LIMIT 500000); > > > Background: > I want to delete all rows user specific except the last n entries. So i > get the currentcount per user first and delete currentCount-maxcount = > limit. > other possibility would be this (but slow) and doesn't work either cuz of > order by > DELETE FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID NOT IN (SELECT ID FROM > ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE DESC LIMIT maxCount); > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
