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) but it seems to work
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.
