Re: sorting by day of the week

Hi,

Try to use a calculated index:

(tested similar solution, but not this code)

CREATE OR REPLACE FUNCTION TestOrder (nameTable.weekDay%TYPE) RETURNS INT AS
'

DECLARE

numWeekDay INT;

BEGIN

if ($1 = ''Wed'') then numWeekDay := 1;

if ($1 = ''Tue'') then numWeekDay := 2;

.....

RETURN (numWeekDay);

END;

' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

CREATE INDEX idx_TestOrder

ON nameTable USING btree (TestOrder(nameTable.weekDay));

SELECT * FROM trajecte ORDER BY TestOrder(nameTable.weekDay);

Regards

----- Original Message -----
From: "Joseph Shraibman" <jks(at)selectacast(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Wednesday, January 25, 2006 2:23 AM
Subject: [SQL] sorting by day of the week

> p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
> date_trunc('day', logtime) > current_date + '7 day ago'::interval group by
> to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;
> to_char | count
> ---------+-------
> Wed | 1447
> Tue | 618
> Thu | 1161
> Sun | 230
> Sat | 362
> Mon | 760
> Fri | 1281
> (7 rows)
>
> The problem is that I want those results sorted in day of week order, not
> text order of the day name, so I tried this:
>
> p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM sclog WHERE
> date_trunc('day', logtime) > current_date + '7 day ago'::interval group by
> to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'D') DESC;
> ERROR: column "sclog.logtime" must appear in the GROUP BY clause or be
> used in an aggregate function
>
> Now obviously I don't want to group by logtime (a timestamp) so how do I
> work around this? What I really need is a function that converts from the
> char representation to a day of week number or vice versa. I also have
> the same problem with month names.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>