Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Distinct based on One column



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..."









Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group