I'm with a problem on postgresql select.
This query works fine:
SELECT
DISTINCT(COLUMN_A || ' - ' || COLUMN_B),
COLUMN_A,
COLUMN_B
FROM
TABLE_A
But, when I include a new colmun on select like that:
SELECT
DISTINCT(COLUMN_A || ' - ' || COLUMN_B),
COLUMN_A,
COLUMN_B,
COLUMN_C
FROM
TABLE_A
The number of result grow's up, and the column with DISTINCT has repeated on result set.
What is going on?
distinct is NOT a function. Using distinct (a), b is the same as
distinct a, b or distinct a, (b). You simply put the columns between parentheses (which isn't such a good idea to begin with because that creates an anonymous record in Postgres).
In Postgres you can use distinct on () for what you are trying to do (at least I think that's what you are trying to do):
SELECT distinct on (column_a, column_b)
COLUMN_A || ' - ' || COLUMN_B,
COLUMN_A,
COLUMN_B
FROM TABLE_A
order by column_a, column_b
The above will only work in Postgres. If you are looking for a portable version based on the SQL standard, you can use a window function for this:
select column_a, column_b, column_c
from (
select column_a, column_b, column_c,
row_number() over (partition by column_a, column_b order by something) as rn
from table_a
) t
where rn = 1;
The order by something is needed to pick one of the duplicate rows.