Distinct based on One column - Mailing list pgsql-novice

From Ketema Harris
Subject Distinct based on One column
Date
Msg-id 0B0FFADA-64E0-4ECD-BDB7-5233F163BA98@gmail.com
Whole thread Raw
List 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..."







pgsql-novice by date:

Previous
From: Lewis Cunningham
Date:
Subject: Re: [SQL][GENERAL] Any documatation about porting from Oracle to PostgreSQL
Next
From: Richard Broersma Jr
Date:
Subject: Re: Problem wyth Case-sensitive ORDER BY