OraFAQ Forum: SQL & PL/SQL » distinct timestamp?

Home » SQL & PL/SQL » SQL & PL/SQL » distinct timestamp? (9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

distinct timestamp? [message #452335]

Wed, 21 April 2010 07:10

koesmet
Messages: 9
Registered: March 2010
Location: munich

Junior Member

Hi all,

how can I distinct this query to get just one entry for one day.

this query:
select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp

returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.

but i want to have:
Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.

thanx for your advises..

Report message to a moderator

Re: distinct timestamp? [message #452336 is a reply to message #452335]

Wed, 21 April 2010 07:25

panyam
Messages: 146
Registered: May 2008

Senior Member

Something like this:

select distinct to_char(Timestamp_field,'dd-Mon-yyyy') date_field
from table1

or

did i misread your post ?

Report message to a moderator

Re: distinct timestamp? [message #452338 is a reply to message #452336]

Wed, 21 April 2010 07:41

koesmet
Messages: 9
Registered: March 2010
Location: munich

Junior Member

it doesnt work:

select distinct to_char(TIMESTAMP, 'YYYY-MON-DD') datum from event_table where id=15 ORDER by timestamp

returns:ORA-01791 not a selected expression

and also:
select distinct to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD') datum from event_table where id=15 ORDER by timestamp

returns: ORA-01791 not a selected expression

Report message to a moderator

Re: distinct timestamp? [message #452339 is a reply to message #452338]

Wed, 21 April 2010 07:44

koesmet
Messages: 9
Registered: March 2010
Location: munich

Junior Member

ok.., without "order by" it works.. Smile

thanks..

Report message to a moderator

Re: distinct timestamp? [message #452340 is a reply to message #452338]

Wed, 21 April 2010 07:45

panyam
Messages: 146
Registered: May 2008

Senior Member

Quote:

ORA-01791: not a SELECTed expression
Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

[Updated on: Wed, 21 April 2010 07:49] by Moderator

Report message to a moderator

Re: distinct timestamp? [message #452341 is a reply to message #452339]

Wed, 21 April 2010 07:48

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

koesmet wrote on Wed, 21 April 2010 08:44

ok.., without "order by" it works..
thanks..

Use position rather than column name.

You can use ORDER BY if you use it as ORDER BY 1 (but you will need to use the TO_DATE on the column or else it will sort it alphabetically rather than by date).

Report message to a moderator

Previous Topic:	Need to create a view
Next Topic:	returning dbms_xmlgen.getxml into a varchar2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 08 06:09:28 CDT 2025