Re: Distinct in query results in wrong order by
Date: Thu, 19 Jun 2003 08:52:58 -0500
Message-ID: <eof3fvclt8pbjvja6sti40duc5drj734rp_at_4ax.com>
Try a trick to fool the order by statement
column date_column noprint
select distinct date_column,to_char(date_column, 'day dd mm yyyy'),next_col...
order by date_column
It should sort by date order but not 'show' the sort column..
Here's an example:
SQL> column birth_dt noprint
1 select birth_dt,to_char(birth_dt,'day dd mm yyyy'), office_nm from hr_cur_rptng
2 where rownum < 12
3* order by birth_dt
SQL> /
TO_CHAR(BIRTH_DT,'DA OFFICE_NM
-------------------- ---------------------------------------------wednesday 05 04 1933
thursday 24 08 1939 Audit
friday 16 08 1946 Bridge
thursday 15 07 1948 Land Management
wednesday 05 10 1949 Metro District
monday 02 07 1951 EEO & Contract Management friday 01 08 1952 Finance
saturday 22 11 1952 Information Technology friday 06 11 1953 Freight and Commercial Vehicle Op's. friday 30 07 1954 District 6A - Rochester tuesday 20 04 1965 Metro District
11 rows selected.
have fun...
r.pit_at_raadvanstate.nl (Roelof) wrote:
>We have query like this
>
>select distinct to_char(date_column, 'day dd mm yyyy')
>, next column
>from table
>order by date_colum
>
>It runs OK without the distinct. But with distinct it returns "not a
>selected expression". The next query runs. But it sorts om the name of
>the days insted of the date_column value as intended.
>
>select distinct to_char(date_column, 'day dd mm yyyy')
>, next column
>from table
>order by to_char(date_column, 'day dd mm yyyy')
>
>Help would be highly appreceated.
>
>Roelof
----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Thu Jun 19 2003 - 15:52:58 CEST