hi i hav a table as such:
id fld1 fld2 updatedate
------------------------------
1 aaa aaa 14-jan-2003
2 bbb bbb 14-jan-2003
3 ccc ccc 15-jan-2003

and i wish to generate a result set that shows rows where the updatedate is unique but i want to include ALL columns in that result set. If there are rows with the same updatedate i just want the first one.

Hi,

Please try

select id, fld1, fld2, distinct updatedate from table name

I am not sure if it is possible to get all distinct columns without
lisiting all the column names in the query

Reg
Usha

select id, fld1, fld2, updatedate
from yourtable zz
where id =
( select min(id)
from yourtable
where updatedate = zz.updatedate )

rudy
http://r937.com/

In a similar vein, I would also like to remove duplicate rows while displaying all columns in the query. Clnumber is the column I want to key on for duplicates. Here is a very slimmed down (there are many more columns & joins) example of the query with some sample data:

select rolodex.ipname, clients.clnumber, classes.class, classes.date
from clients
join rolodex on clientid = rolodexid
join classes on clientid = classid
where rolodexid = 3

ipname clnumber class date
-----------------------------------
smith 11111 320 2004-12-21
smith 11111 420 2004-12-22
barnes 22222 320 2004-12-21
jones 33333 320 2004-12-21
jones 33333 320 2004-12-21

Here is what I want the results to be (I don't care which of the duplicate rows get eliminated, although I suppose I could use class with an aggregate like in Rudy's prior example):
ipname clnumber class date
-----------------------------------
smith 11111 320 2004-12-21
barnes 22222 320 2004-12-21
jones 33333 320 2004-12-21

I tried the following but got no results:
select * from
(
select rolodex.ipname, clients.clnumber, classes.class, classes.date
from clients
join rolodex on clientid = rolodexid
join classes on clientid = classid
where rolodexid = 3
) As IPN
Where class =
(Select MIN(class) FROM classes
where clnumber = IPN.clnumber)

Below is vendor specific(DB2) SQL using OLAP functions. I think Oracle should support the same syntax (except temp table).

with temp(ipname, clnumber, class, date) as
(values
('smith', 11111, 320, '2004-12-21'),
('smith', 11111, 420, '2004-12-22'),
('barnes', 22222, 320, '2004-12-21'),
('jones', 33333, 320, '2004-12-21'),
('jones', 33333, 320, '2004-12-21')
)

select ipname, clnumber, class, date from
( select
ipname, clnumber, class, date,
rownumber() over (partition by clnumber) as r
from temp) t
where r = 1


IPNAME CLNUMBER CLASS DATE
------ ----------- ----------- ----------
smith 11111 320 2004-12-21
barnes 22222 320 2004-12-21
jones 33333 320 2004-12-21

3 record(s) selected.

Thanks for your reply, chuzhoi. I'm on SQL Server, so I'm not sure if this will work. However, I will look into it. I appreciate the effort.

For MS-SQL, and assuming there were no time values in the updatedate column, I'd use:-PatP