how to retrieve distinct records [message #19058] |
Wed, 27 February 2002 02:12 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
Hi,
i am having one more problem in this..
i have 5 columns and many tables to which i link and get the final data
col1 col2 col3 col4 col5
--------------------------------
1 one say say1 sa2
1 one abc def geh
1 two he me hello
Now i want to get a distict set of records where col1 having value of 1 (i need only 1 entry to be retrieved)
I have tried the below statement but it gives me
select col1,col2,col3 from tab1 t1 where not exists(select col1 from table2 where t1.col2 = table2.col2) and rowid = (select min(rowid) from tab1 t2 where t1.col1 = t2.col1);
It gives me
col1 col2 ......
------------
1 one
1 two
But i want something like
col1 col2 ....
---------------
1 one
How to do it..
|
|
|
Re: how to retrieve distinct records [message #19118 is a reply to message #19058] |
Thu, 28 February 2002 13:54 |
Raj
Messages: 411 Registered: November 1998
|
Senior Member |
|
|
did you try this
select distinct col1,col2,col3 from tab1 t1 where not exists(select col1 from table2 where t1.col2 = table2.col2) and rowid = (select min(rowid) from tab1 t2 where t1.col1 = t2.col1);
|
|
|