-
hi friends,
I have creted table TBL with structure as below.
SQL> desc tbl
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(5)
COL2 VARCHAR2(5)
KEY VARCHAR2(5)
Sample data is:
COL1 COL2 KEY
----- ----- -----
y val2 ST
x val1 ST
I need query to return output.
select col1,col2 from tbl where key='ST';
If col1 has only x , print corresponding row. If col1 has only y , print corresponding row. If col1 has two values both x and y, print first row only.
Thanks,
Bhaskara
-
What does "print first row only" mean? How do you determine the "first row"?
-
The first part is:
select t.* from tbl t, (select key, count(*) keycount from tbl group by key) t2
where t2.keycount = 1
and t.key = t2.key
-
If you realy want a result for a *single* key, then the sollution is trivial -simply add the "WHERE ROWNUM=1":
select col1,col2 from tbl where key='ST' and rownum=1;
But if you want a result set for many keys (single row per key), then use this:
select col1, col2, key from tbl
where rowid in (select min(rowid) from tbl group by key);
HTH,
-
Is it that if the KEY column contains X and Y you want to print the row with the X value in COL1? and pl let me know how the COL1 can have value X and Y ? Is it like X Y or X,Y etc?
Thanks,
Rajesh
