pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsorting by day of the week
3+ messages / 2 participants
[nested] [flat]
* sorting by day of the week
@ 2006-01-25 01:23 Joseph Shraibman <jks@selectacast.net>
0 siblings, 2 replies; 3+ messages in thread
From: Joseph Shraibman @ 2006-01-25 01:23 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sorting by day of the week
@ 2006-01-25 02:39 Joseph Shraibman <jks@selectacast.net>
parent: Joseph Shraibman <jks@selectacast.net>
1 sibling, 0 replies; 3+ messages in thread
From: Joseph Shraibman @ 2006-01-25 02:39 UTC (permalink / raw)
To: pgsql-sql
Nevermind, I figured out that I just needed to do it like this:
SELECT to_char( logtime, 'Dy'),count(*),to_char( logtime, 'D') FROM
sclog WHERE date_trunc('day', logtime) > current_date + '7 day
ago'::interval group by to_char( logtime, 'Dy'),to_char( logtime, 'D')
ORDER BY to_char( logtime, 'D') DESC;
It is interesting that I can't put to_char( logtime, 'D') in the the
group by without putting it in the select.
Joseph Shraibman wrote:
> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sorting by day of the week
@ 2006-01-25 03:14 ipv@tinet.org
parent: Joseph Shraibman <jks@selectacast.net>
1 sibling, 0 replies; 3+ messages in thread
From: ipv@tinet.org @ 2006-01-25 03:14 UTC (permalink / raw)
To: pgsql-sql
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@selectacast.net>
To: <pgsql-sql@postgresql.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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-25 03:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-25 01:23 sorting by day of the week Joseph Shraibman <jks@selectacast.net>
2006-01-25 02:39 ` Joseph Shraibman <jks@selectacast.net>
2006-01-25 03:14 ` ipv@tinet.org
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox