Oracle version 11g
---------------------
I had a question regarding usage of Distinct in a query. The distinct clause in oracle is selecting the result set from the query conditions and
then performing a distinct on the result set . This approach is reducing the number of rows expected. can you please help how to fetch the expected
results shown below
Table
--------
CREATE TABLE TEST_RESULTSET
( COL_A VARCHAR2(10) ,
COL_B VARCHAR2(10) ,
COL_C NUMBER(10)) ;
COMMIT ;
Rows
-----
INSERT INTO TEST_RESULTSET VALUES ( 'AAA' , 'BBB' , 1 );
INSERT INTO TEST_RESULTSET VALUES ( 'AAA' , 'BBB' , 2 );
INSERT INTO TEST_RESULTSET VALUES ( 'AAA' , 'BBB' , 3 );
INSERT INTO TEST_RESULTSET VALUES ( 'CCC' , 'DDD' , 4 );
INSERT INTO TEST_RESULTSET VALUES ( 'DDD' , 'DDD' , 5 );
COMMIT ;
query to show the data in the table
-----------
SELECT * FROM TEST_RESULTSET ;
COLA COLB COLC
AAA BBB 1
AAA BBB 2
AAA BBB 3
CCC DDD 4
DDD DDD 5
Query to fetct the distinct values of COL_A and COL_B
SELECT DISTINCT COL_A , COL_B
FROM TEST_RESULTSET
WHERE ROWNUM <= 3 ;
COLA COLB
AAA BBB
Expected result is
COLA COLB
AAA BBB
CCC DDD