Distinct rows get from a column. Oracle

Question

I want to display distinct rows based on the operator. Like

<code>Sample</code>

$query = oci_parse($con, "SELECT POST.*, USER_LOGIN.*, STATION.*, ROLE.* 
                          FROM USER_LOGIN 
                          INNER JOIN STATION ON USER_LOGIN.S_ID = STATION.S_ID
                          INNER JOIN POST ON USER_LOGIN.USER_ID = POST.USER_ID
                          INNER JOIN ROLE ON USER_LOGIN.ROLE_ID = ROLE.ROLE_ID
                          WHERE ROLE_NAME = 'Operator' ");

Any help would be appreciated.

The below error now i am getting. I don't know whats wrong in it.

Error: ORA-00936: missing expression

$query = oci_parse($con,"SELECT * FROM 

        (SELECT POST.*, USER_LOGIN.*, STATION.*, ROLE.*, IR.*,
        row_number() over(PARTITION BY ROLE_NAME ORDER BY ROLE_NAME) AS seqnum

        FROM USER_LOGIN

        INNER JOIN
        STATION
        ON USER_LOGIN.PS_ID = STATION.PS_ID

        INNER JOIN
        POST
        ON USER_LOGIN.USER_ID = POST.USER_ID

        INNER JOIN
        ROLE
        ON USER_LOGIN.ROLE_ID = ROLE.ROLE_ID

        INNER JOIN
        IR
        ON USER_LOGIN.USER_ID = IR.USER_ID

        WHERE ROLE_NAME = 'Operator') AS t

        WHERE t.seqnum = 1");

Gordon Linoff · Accepted Answer · 2014-03-30T14:12:09.753

My guess is that you want to specify the particular columns and use distinct (or group by):

SELECT DISTINCT operator, cnic, station 
FROM USER_LOGIN INNER JOIN
     STATION
     ON USER_LOGIN.S_ID = STATION.S_ID INNER JOIN
     POST
     ON USER_LOGIN.USER_ID = POST.USER_ID INNER JOIN
     ROLE
     ON USER_LOGIN.ROLE_ID = ROLE.ROLE_ID
WHERE ROLE_NAME = 'Operator';

EDIT:

If you want one arbitrary row, then do something like this:

SELECT *
FROM (SELECT POST.*, USER_LOGIN.*, STATION.*, ROLE.*,
             row_number() over (partition by operator, cnic, station order by operator) as seqnum
      FROM USER_LOGIN INNER JOIN
           STATION
           ON USER_LOGIN.S_ID = STATION.S_ID INNER JOIN
           POST
           ON USER_LOGIN.USER_ID = POST.USER_ID INNER JOIN
           ROLE
           ON USER_LOGIN.ROLE_ID = ROLE.ROLE_ID
      WHERE ROLE_NAME = 'Operator'
     ) t
WHERE seqnum = 1;

The row_number() function enumerates rows in groups. Each group starts over at 1. The grouping is defined by partition by, so rows with the same value of operator, cnic, and station will be in a grouping. The order by specifies the order of the numbers within a group. Here is uses a constant for the group, so it is arbitrary. The final step is where seqnum = 1, which chooses one row from each grouping.

the answer given here something like that i want but i didn't get the query http://stackoverflow.com/questions/966176/select-distinct-on-one-column — user3400389, Mar 30 '14 at 13:47
it giving me this ` oci_execute(): ORA-00923: FROM keyword not found where expected`. I am trying to solve this issue — user3400389, Mar 30 '14 at 14:07
@user3400389 . . . Try listing the columns explicitly. You may have an issue with duplicate columns with the version there now. — Gordon Linoff, Mar 30 '14 at 14:12

score 1 · Answer 2 · answered Mar 30 '14 at 13:29

1

From : http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF55166

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected.

Just update you code like this :

$query = oci_parse($con, "SELECT DISTINCT  POST.*, USER_LOGIN.*, STATION.*, ROLE.* 
    FROM USER_LOGIN 
    INNER JOIN STATION ON USER_LOGIN.S_ID = STATION.S_ID
    INNER JOIN POST ON USER_LOGIN.USER_ID = POST.USER_ID
    INNER JOIN ROLE ON USER_LOGIN.ROLE_ID = ROLE.ROLE_ID
    WHERE ROLE_NAME = 'Operator' ");

answered Mar 30 '14 at 13:29

Pascamel

953
6
18
28

No this didn't work because dintinct is applying to all the tables. i just want to applay distinct to the column `ROLE_NAME` – user3400389 Mar 30 '14 at 13:35
DISTINCT is applied to all columns returned by your query, so update your "SELECT" line accordingly. Or maybe update your question with better data samples, for now your sample of output has 3 columns and your select return all columns for 4 tables so it's hard to help you further – Pascamel Mar 30 '14 at 13:39
yes you are right. however still trying my best to make it right – user3400389 Mar 30 '14 at 13:40
added above @Pascamel – user3400389 Mar 30 '14 at 13:44

Distinct rows get from a column. Oracle

2 Answers2