Deleting similar columns in SQL?

Count is a sql reserve word so it'll have to be escaped somehow. I can't remember the syntax for doing that in Postgres off the top of my head so I just surrounded it with square braces (change it if that isn't correct). In any case, the following should theoretically work (but I didn't actually test it): delete from widgets where id not in ( select max(w2.Id) from widgets as w2 inner join (select max(w1.

Count) as count, type from widgets as w1 group by w1. Type) as sq on sq.Count=w2. Count and sq. Type=w2.

Type group by w2. Count ).

Thanks, that did the trick! :) I wasn't using "count" as the actual column name, this is just a dumbed down example that gets to the heart of what I'm trying to do. I appreciate the help!

– Matt Huggins Nov 2 '09 at 6:08 @Matt Huggins: You're welcome :) – Asaph Nov 2 '09 at 6:24.

There is a slightly simpler answer than Asaph's, with EXISTS SQL operator : DELETE FROM widgets AS a WHERE EXISTS (SELECT * FROM widgets AS be WHERE (a. Type = b. Type AND b.

Count > a. Count) OR (b. Id > a.Id AND a.

Type = b. Type AND b. Count = a.

Count)) EXISTS operator returns TRUE if the following SQL statement returns at least one record.

According to your requirements, seems to me that this should work: DELETE FROM widgets WHERE type NOT IN ( SELECT type, MAX(count) FROM widgets GROUP BY type ).

If I am right, your query don't delete anything! – Erlock Nov 10 '09 at 10:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.