Need SQL Query help

[Bhaskara]

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

[marist89]

What does "print first row only" mean? How do you determine the "first row"?

[marist89]

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

[jmodic]

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,

[rborikar]

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