CODEWITHSUNDEEP
sqloracle-database
user1921479
user1921479

Reputation: 255

ORACLE - Select ROWNUM with distinct multiple COLUMNS

Hi I am invoking SQL quires from java application, Since table has huge data its taking time to process from java side. Now i am invoking 1000 by 1000 records

SELECT t1.col1,t1.col2,t2.col1,t2.col2 FROM(SELECT rownum AS rn,
t1.col1,
t1.col2,
t2.col1,
t2.col2 FROM table1 t1,
table2 t2 WHERE t1.id=t2.id) WHERE rn BETWEEN ? AND ?;

But i have one more query which has distinct values like below

 SELECT t1.col1,t1.col2,t2.col1,t2.col2 FROM(SELECT rownum AS rn,
distinct t1.col1,
t1.col2,
t2.col1,
t2.col2 FROM table1 t1,
table2 t2 WHERE t1.id=t2.id) WHERE rn BETWEEN ? AND ?;

But this query is giving error, Not allowing to add rownum AS rn for distinct. Please could you help us to resolve above use case?

Upvotes: 0

Views: 1546

Answers (1)

Timothy Truckle
Timothy Truckle

Reputation: 15622

In an oracle database the DISTINCT key word is only allowed directly behind the SELECT key word or inside the COUNT function.

Furthermore your SQL will lead to inconsistent results since in an oracle database the order of records is not guaranteed without explicit ORDER BY clause.

you also cannot access the tabla aliases from an inner select so you have to apply column aliases if the columns in the different tables have the same column name.

The best solution would be to to add another layer of nested selects:

 SELECT t1_col1, t1_col2, t2_col1, t2_col2
   FROM (
    SELECT rownum AS rn, inner_tab.*
      FROM (
       SELECT distinct t1.col1 AS t1_col1,
                       t1.col2 AS t1_col2,
                       t2.col1 AS t2_col1,
                       t2.col2 AS t2_col2
         FROM table1 t1,
              table2 t2 
        WHERE t1.id=t2.id
      ORDER BY 1 -- you have to decide! 
     ) inner_tab
   ) WHERE rn BETWEEN ? AND ?;

Upvotes: 2

Related Questions