Re: Distinct not working when convert DATE using TO_CHAR

SunScreen wrote:
> Following is the data:
>
> Number Date
> ---------- -------------------
> 26 12/19/2005 13:57:05
> 26 12/19/2005 13:57:07
> 27 12/19/2005 13:57:29
> 27 12/19/2005 13:57:31
> 28 12/19/2005 13:57:54
> 28 12/19/2005 13:57:55
>
> and the actual query:
>
> select Field1 "Number", to_char(time, 'MM/DD/YYYY HH24:MI:SS') "Date"
> from audit_iedata where ( time >= to_date('19-DEC-2005 13:00',
> 'DD-MON-YYYY:HH24:MI') and time <= to_date('19-DEC-2005 14:00',
> 'DD-MON-YYYY:HH24:MI'));
>
> How can I get only first occurence of Field1 ?

Yet another "first of" question.

A result set DOES NOT have a defined order, so asking for the first one of the set of rows where number=26 is a silly question.

Now you were told about the group by and that seems to be the tool you should use. So if first of means the first in time and you do have a time column. And infact you said this is the result you want:
> ie:
>
> Number Date
> ---------- -------------------
> 26 12/19/2005 13:57:05
> 27 12/19/2005 13:57:29
> ....

so a group by on field1/Number selecting the MIN(Date) will work (again unless you definition of "first of" is something else, in which case you use that something else in the group by)

Ed
(what happened to people understanding the technology underlying the tools they use?) Received on Wed Dec 21 2005 - 07:16:36 CST