Distinct based on One column

From: Ketema Harris <ketema(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Distinct based on One column
Date: 2006-10-20 21:07:04
Message-ID: 0B0FFADA-64E0-4ECD-BDB7-5233F163BA98@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a table structure as so:

CREATE TABLE tpv_questions
(
tpv_question_id serial NOT NULL,
marketer_product_id int4,
customer_status varchar,
question_type varchar NOT NULL,
customer_type_id int4 NOT NULL,
question_order int4,
question_id int NOT NULL, -- The Question FK to a questions table
CONSTRAINT "PK_tpv_question_id" PRIMARY KEY (tpv_question_id),
CONSTRAINT "FK_tpv_questions_question_text_id" FOREIGN KEY
(question_id)
REFERENCES question_text(question_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION
);

Create Table question_text
(
question_id serial NOT NULL,
question_text text NOT NULL
CONSTRAINT "PK_question_id" PRIMARY KEY (question_id)
);

I then have a function in which I do some stuff...

CREATE OR REPLACE FUNCTION get_tpv_questions(_marketer_product_id
integer[], _customer_status "varchar", _customer_type_id int4)
RETURNS record AS
$BODY$
declare
_result record;
_question_id_list text;
_question_type_list text;
_question_order_list text;
_question_list text;
_data_inputs_list text;

begin
for _result in select question_id::text, question_type,
question_order::text, question_text
from tpv_questions
inner join question_text
on tpv_questions.question_id = question_text.question_id
where marketer_product_id = any(_marketer_product_id) and upper
(customer_status) = upper(_customer_status)
and customer_type_id = _customer_type_id
order by question_order loop
--I do some stuff here that I am happy with
return _result;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

My question is on the query that I am looping over: select
question_text.question_id::text, question_type, question_order::text,
question_text
from tpv_questions
inner join question_text
on tpv_questions.question_id = question_text.question_id
where marketer_product_id = any(_marketer_product_id) and upper
(customer_status) = upper(_customer_status)
and customer_type_id = _customer_type_id
order by question_order, question_id

The result set contains duplicate questions when in my client
application multiple products are selected. Currently I filter out
the duplicates in the client application. My question is how to get
SQL to return only rows where the question is not repeated even
though other data in the row is not DISTINCT. Example result would
contain:
110,"BOOL",1,"I need to confirm..."
126,"BOOL",1,"I need to confirm..."
78,"BOOL",1,"I need to confirm..."

Each Question is the same, and even though it appears on different
"products" i really only need one of the rows. What I am looking for
is a way to say: "If a question we have already retrieved shows up
again, regardless of other column data, discard it and continue..."

Browse pgsql-novice by date

  From Date Subject
Next Message Richard Broersma Jr 2006-10-21 03:35:50 Re: Problem wyth Case-sensitive ORDER BY
Previous Message radgar 2006-10-20 10:07:00 Problem with migration