Remove duplicates from the table by using two columns in sql?

Question

I have table like this

Id Name Lang   templateid
1  xxx  ENGISH  1
2  xxx  SPANISH 2
3  xxx  ENGISH  1
4  xxx  ENGISH  1
5  xxx  ENGISH  1
6  xxx  SPANISH 1

I want to remove the duplicate records which have same lang and templateid.For example templateid 1 + Lang ENGLISH is repeated more than once .

Delete the duplicates records and leave latest one ie., Id 5 is latest record for english language.

Any suggestions would be appreciated.

Answer 1

MySQL

Query

DELETE t2
FROM   tbl t1
JOIN   tbl t2 ON 
(
   t2.lang = t1.lang
   AND t2.templateId=t1.templateId
   AND t2.id < t1.id
);

Fiddle Demo

---

---

PostgreSQL

Query

DELETE FROM tbl t1
WHERE id IN 
(
  SELECT t1.id
  FROM   tbl t1
  JOIN   tbl t2 ON 
  (
    t2.lang = t1.lang
    AND t2.templateId=t1.templateId
    AND t2.id > t1.id
  )
);

Fiddle Demo