Eliminate duplicate data

Question

Query

SELECT
  ppc.personid,
  ppc.category,
  ppc.phonenumber,
  ppc.createts
FROM
  person_phone_contacts ppc
WHERE  
      CURRENT_TIMESTAMP BETWEEN ppc.createts AND ppc.endts
  AND CURRENT_DATE BETWEEN ppc.effectivedate AND ppc.enddate
ORDER BY
  ppc.personid, ppc.category, ppc.createts DESC

Resulting Data

3742 | Home   | xxx-xxx-xxxx | 2009-09-09 11:59:00.357-04
3742 | Home   | xxx-xxx-xxxx | 2009-08-04 20:13:17.161-04*
3742 | Mobile | xxx-xxx-xxxx | 2009-09-09 11:59:20.070-04
3742 | Mobile | xxx-xxx-xxxx | 2009-09-09 11:59:20.070-04*
3742 | Other  | xxx-xxx-xxxx | 2009-08-04 20:13:17.161-04

^*Desired duplicates to discard.

Desired Data

3742 | Home   | xxx-xxx-xxxx | 2009-09-09 11:59:00.357-04
3742 | Mobile | xxx-xxx-xxxx | 2009-09-09 11:59:20.070-04
3742 | Other  | xxx-xxx-xxxx | 2009-08-04 20:13:17.161-04

Question

What is the most efficient way to retrieve a single phone number per category per person, using the most recent date (even when multiple phone numbers in the same category for the same person have the same date)?

Possible Solutions

Use DISTINCT ON (ppc.category) category can limit the results per person, but how to apply it to all people, individually?

Constraints

• PostgreSQL 8.3
• No stored functions or procedures

Thank you!

Answer 1

Assuming that (personid, category, createts) is unique...

SELECT
  ppc.personid,
  ppc.category,
  ppc.phonenumber,
  ppc.createts
FROM
  person_phone_contacts AS ppc
  INNER JOIN (
    SELECT
      personid,
      category,
      MAX(createts) AS newest_createts
    FROM
      person_phone_contacts
    WHERE  
          CURRENT_TIMESTAMP BETWEEN createts AND endts
      AND CURRENT_DATE BETWEEN effectivedate AND enddate
    GROUP BY
      personid, category
  ) AS ppc2
  ON ppc.personid = ppc2.personid
    AND ppc.category = ppc2.category
    AND ppc.createts = ppc2.newest_createts

I'm not familiar with Postgres' SQL dialect (using MSSQL myself, where this could be solved more elegantly), but since this is pretty much standard SQL I think it should work.