Re: Sorting results by date

One possibility is to set nls_date_format for the session and then sort on trunc(logintime).

The nls_date_format will guarantee the character display of the date that you wish, and ordering by trunc(logintime) will keep the date ordering.

For example, the following might work:

SQL> alter session set nls_date_format = 'MON-DD-YYYY'; SQL> select username, trunc(logintime), count(*)   2 from applogin
  3 group by username, trunc(logintime)   4 order by trunc(logintime);

Frank Hubeny

stuartirvine_at_my-deja.com wrote:

> I have a table which records when a user logs into the application
> table applogin
> username varchar2(30),
> logintime date,
> etc.
>
> I need to create a report which is grouped by day, and counts the
> number of times a user logs in each day.
>
> select username, to_char(logintime,'MON-DD-YYYY'), count(*)
> from applogin
> group by username, to_char(logintime,'MON-DD-YYYY')
> order by to_char(logintime,'MON-DD-YYYY')
>
> This does the trick, but not in the right order.
> I need to sort the results by login date, by day. This query will sort
> the results by char order not date order i.e. Jan 1 , Jan 2 etc..
>
> Any sugestions ???
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
  Received on Thu Aug 24 2000 - 21:01:55 CDT