Selecting distinct rows - oracle 9 sql

Posted on 2008-11-18
Last Modified: 2013-12-19
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.

Question by:tonMachine100
    LVL 27

    Accepted Solution

    Try this (For 9i)
    FROM (
    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

    LVL 22

    Expert Comment

    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?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now