CODEWITHSUNDEEP
phpsqloracle-databaseoracle-call-interface
user3400389
user3400389

Reputation: 367

Distinct rows get from a column. Oracle

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");

Upvotes: 0

Views: 312

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 2

Pascamel
Pascamel

Reputation: 953

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' ");

Upvotes: 1

Related Questions