• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

Selecting distinct rows - oracle 9 sql

Please see the attached which displays a) the current output and b) the desired output.

This data is looking at a questions and answers given in two electronic forms for two different clients. The question text appears in the QST_DESC column. Any answers given will appear in the AVD_DATA column. If an answer has been provided, the string DATA will appear in the ANV_NAME column.

I would like each question (and answer) row to only appear once for each client- so if the clients question has been answered I would like this row to appear (there will be the text DATA in the ANV_NAME column), if no answer has been provided the row will not have the word DATA in the ANV_NAME column and I would like this row to appear.

I hope this make some sort of sense!  Any help with this is appreciated.

sample.xls
0
tonMachine100
Asked:
tonMachine100
1 Solution
 
SujithData ArchitectCommented:
Try this (For 9i)
select QST_QSE_ID, QST_TYPE ,QST_DESC ,AVD_DATA ,QST_CODE ,ASM_SUBJECT_ID ,ANS_ID ,ANV_NAME ,ANS_QST_CODE ,FIRST_NAME ,QQS_SEQ ,ANS_SEQ ,QST_ID
FROM (
SELECT 
QST_QSE_ID, QST_TYPE ,QST_DESC ,AVD_DATA ,QST_CODE ,ASM_SUBJECT_ID ,ANS_ID ,ANV_NAME ,ANS_QST_CODE ,FIRST_NAME ,QQS_SEQ ,ANS_SEQ ,QST_ID,
row_number() over(partition by FIRST_NAME, QST_QSE_ID order by ANV_NAME) rn
from <your table>
)
where rn = 1

Open in new window

0
 
dportasCommented:
What key(s) exist in your table? When there is more than one answer for a question which one do you want to return? Do you want to pick one at random or should some rule be applied?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now