Home » SQL & PL/SQL » SQL & PL/SQL » how to get unique value in column ? (11g)
how to get unique value in column ? [message #569361] Thu, 25 October 2012 04:56 Go to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi all,

hope doing well,

sir i am using one query which should return unique value

i have one table that has one column punchdate datatype is date.

here value is stored in two rows that is

1. 24-10-12

2. 24-10-12

and my query is this:
   SELECT distinct(PunchDate) FROM Trans_RawProcessDailyData  ORDER BY PunchDate ASC;

but still getting two values.

why?

thanks
Re: how to get unique value in column ? [message #569363 is a reply to message #569361] Thu, 25 October 2012 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again because there is a TIME part in DATE.
How many times did we tell you this? 10? 20?

Regards
Michel
Re: how to get unique value in column ? [message #569364 is a reply to message #569363] Thu, 25 October 2012 05:01 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
then how to get unique date here.

thanks
Re: how to get unique value in column ? [message #569367 is a reply to message #569364] Thu, 25 October 2012 05:05 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
got it now it's working.

thanks for your suggestion.
Re: how to get unique value in column ? [message #569371 is a reply to message #569367] Thu, 25 October 2012 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you got it, you have to post it.

Regards
Michel
Re: how to get unique value in column ? [message #569418 is a reply to message #569371] Thu, 25 October 2012 14:13 Go to previous messageGo to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
i suppose doing a distinct trunc on that field or a distinct to_char would solve that issue
Re: how to get unique value in column ? [message #569420 is a reply to message #569418] Thu, 25 October 2012 19:02 Go to previous messageGo to next message
BlackSwan
Messages: 22528
Registered: January 2009
Senior Member
>i suppose doing a distinct trunc on that field or a distinct to_char would solve that issue
what do you see when you invoke the SQL?
Re: how to get unique value in column ? [message #569425 is a reply to message #569420] Fri, 26 October 2012 00:28 Go to previous messageGo to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
distinct dates.
Re: how to get unique value in column ? [message #569426 is a reply to message #569425] Fri, 26 October 2012 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
wrong.
Re: how to get unique value in column ? [message #569427 is a reply to message #569426] Fri, 26 October 2012 03:08 Go to previous messageGo to next message
John Watson
Messages: 4403
Registered: January 2010
Location: Global Village
Senior Member
What do you mean, Michel? Using trunc like this works -
orcl>
orcl> select distinct(d) from
  2  (select sysdate d from dual
  3  union all
  4  select sysdate+1/1440 from dual);

D
-----------------
26-10-12 09:07:59
26-10-12 09:08:59

orcl> ed
Wrote file afiedt.buf

  1  select distinct(d) from
  2  (select trunc(sysdate) d from dual
  3  union all
  4* select trunc(sysdate+1/1440) from dual)
orcl> /

D
-----------------
26-10-12 00:00:00

orcl>
Re: how to get unique value in column ? [message #569429 is a reply to message #569427] Fri, 26 October 2012 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
so it is "distinct trunc(dates)" not "distinct dates".

Regards
Michel
Re: how to get unique value in column ? [message #569430 is a reply to message #569429] Fri, 26 October 2012 03:38 Go to previous message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
yes what i was implying is what john did i tested with to_chars as well, although the trunc would just be the best method
select distinct to_char(d, 'dd-mm-yy') 
  from (select sysdate d from dual
         union all
        select sysdate+1/1440 from dual);
Previous Topic: if data is not there in table why the procedure getting stuck?
Next Topic: opposite of listagg
Goto Forum:
  


Current Time: Wed Jul 30 10:15:37 CDT 2014

Total time taken to generate the page: 0.16347 seconds