33 lines
600 B
SQL
33 lines
600 B
SQL
DROP TABLE abook;
|
|
|
|
CREATE TABLE
|
|
abook (count, name, address)
|
|
AS SELECT
|
|
DISTINCT ON(lower(address))
|
|
COALESCE(named.count, 1),
|
|
COALESCE(named.name, lower(unnamed.address)),
|
|
lower(unnamed.address)
|
|
FROM
|
|
contact unnamed
|
|
LEFT OUTER JOIN (
|
|
SELECT
|
|
DISTINCT ON (lower(address))
|
|
count(name),
|
|
name,
|
|
lower(address) AS laddr
|
|
FROM
|
|
contact
|
|
WHERE
|
|
name != ''
|
|
GROUP BY
|
|
lower(address),
|
|
name
|
|
ORDER BY
|
|
lower(address),
|
|
count DESC,
|
|
name
|
|
) named
|
|
ON
|
|
named.laddr = lower(unnamed.address)
|
|
;
|