Some help with Oracle, simple script i can't get to behave

[B@Th*nG]

Having difficultly removing the newest records from a table. I've probably gone about it the wrong way in the first instance but I've made it this far I've created a temporary table, inserted the records (entityid, addressid and startdate of the address) and then just want to remove the newest record for each EntID.

What i want to do is pick out the AddressID's with the newest date and remove them, but i'm getting an error because the addID is not in the select statement:

Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert BLAH BLAH

DELETE FROM ENTADD
WHERE AddID in
	(SELECT MAX(StDate)
	FROM ENTADD
	GROUP BY EntID);

any ideas?

B@

 

[B@Th*nG]

i had tried that, get this error instead:

DELETE FROM ENTADD
WHERE AddID in
(SELECT MAX(StDate), AddID
FROM ENTADD
GROUP BY EntID);

Error report -
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"
*Cause:
*Action:

B@

 

[B@Th*nG]

You'll need to add it in the group by statement as well, as it is not a calculation.

If i group by date or addressID then it won't pick the highest date grouped by entityID so it won't work will it?

An entityID has multiple addressID's, each with a startdate. I need the newest address ID per entityID deleted.

Is it not because your sub query is returning a date and your are trying to match that to the numeric AddID?

I would have thought you want this:

DELETE FROM ENTADD
WHERE StDate in
	(SELECT MAX(StDate)
	FROM ENTADD
	GROUP BY EntID);

That'll just delete any entries with those dates though. The unique identifier is either a combination of entityid AND date or just addressid

B@

 

[B@Th*nG]

DROP TABLE ENTADD;

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (1,2, sysdate+1);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (2,2, sysdate+2);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (3,2, sysdate+3);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (4,2, sysdate+4);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,3, sysdate);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,2, sysdate+5);

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.addid = '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE addid = a.addid)
);

?

Will try that out

Yeah, had spotted that after I posted it. Try this:

DELETE FROM ENTADD A 
WHERE A.StDate in (SELECT MAX(B.StDate)
                         FROM ENTADD B
                         WHERE A.EntID = B.EntID
                         GROUP BY B.EntID);

that'll still delete anything which matches that startdate, irrespective of what entityID it is, because you're just deleting anything with that startdate.

B@

 

[B@Th*nG]

DROP TABLE ENTADD;

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (1,2, sysdate+1);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (2,2, sysdate+2);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (3,2, sysdate+3);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (4,2, sysdate+4);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,3, sysdate);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,2, sysdate+5);

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.addid = '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE addid = a.addid)
);

?

that's not going to remove the newest address from each entityID i dont think

B@

 

[B@Th*nG]

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.EntID= '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE EntID = a.EntID)
);

0 rows deleted, what've i done wrong?

B@

 

[B@Th*nG]

definitely data in the table:



thanks for your help!

B@

 

[B@Th*nG]

yep, that's worked on that record so i tried the below and got another error:

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.EntID = EntID
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE EntID = a.EntID)
)
Error report -
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

B@

 

[B@Th*nG]

thank you, thank you, thank you! perfect!

B@