Hi,
I have a problem in writing correct SQL. Could you please help me?
I greatly appreciate your help.

Table
---------------------
Col1 |Col2| Col3
--------------------
1 2 3
1 2 2
4 5 6
4 5 7
4 5 8
9 9 9
-------------------------

Result
---------------------
Col1 |Col2| Col3
--------------------
1 2 3
4 5 6
9 9 9
----------------------

That is, if two or more rows are matching col1 & col2 then list only the first row.

How do we write a sql for this?

Thank you

--JK

use selfjoin to the table and select min value from third col3. Let me know if you did not get answer.

could you please give the SQL for this example? I am not sure how to use min() on col3. Do we have to use group or order by clause?

Thanks

Try Group by . If you want to keep in the same order of your result then u can keep order by on col1 and col2

in which server, are u working?

select a.col1,a.col2,min(a.col3)
from t1 a, t1 b
where a.col1 = b.col1
and a.col2 = b.col2
group by a.col1,a.col2
order by a.col1,a.col2

try this and send me the result

I think this will also work in simple way, no need to do like in the previous message.

select col1,col2,min(col3)
from t1
group by col1,col2
order by col1,col2

and u will get the result

1 2 2
4 5 3
9 9 9

/* not 1 2 3, Do you want to go with the way you inserted ?? if
yes then go with row-id */

create table tab1(col1 number,col2 number,col3 number);

SET DEFINE OFF;
Insert into TAB1
(COL1, COL2, COL3)
Values
(1, 2, 3);
Insert into TAB1
(COL1, COL2, COL3)
Values
(1, 2, 2);
Insert into TAB1
(COL1, COL2, COL3)
Values
(4, 5, 6);
Insert into TAB1
(COL1, COL2, COL3)
Values
(4, 5, 7);
Insert into TAB1
(COL1, COL2, COL3)
Values
(4, 5, 8);
Insert into TAB1
(COL1, COL2, COL3)
Values
(9, 9, 9);
COMMIT;


select * from tab1 t
where rowid = ( select min(rowid)
from tab1
where col1 = t.col1
and col2=t.col2 );
Result
--------------------
1 2 3
4 5 6
9 9 9

__________________
Sireesha