pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAn order by question
4+ messages / 3 participants
[nested] [flat]
* An order by question
@ 2004-01-24 06:24 David Arnold <darnold@northcoast.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Arnold @ 2004-01-24 06:24 UTC (permalink / raw)
To: pgsql-sql
All,
I need a little help on a sorting problem. Imagine a table, call it
records, that has fields:
lastName
firstName
term
I want to sort the records by last name, then first name, and finally by
term. This almost does what I want:
select * from records order by lastName, firstName, term;
However, the possible values for term are:
2002F
2003S
2003X
2003F
Where F is for fall, S for spring, and X for summer session. Thus, a
straight alphabetical sort doesn't give me what I want. If the year is the
same, then I want a sort with S, then X, then F for identical year.
Any suggestions?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: An order by question
@ 2004-02-01 13:34 Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2004-02-01 13:34 UTC (permalink / raw)
To: darnold@northcoast.com; +Cc: pgsql-sql
> I need a little help on a sorting problem. Imagine a table, call it
> records, that has fields:
>
> lastName
> firstName
> term
>
> I want to sort the records by last name, then first name, and finally by
> term. This almost does what I want:
>
> select * from records order by lastName, firstName, term;
>
> However, the possible values for term are:
>
> 2002F
> 2003S
> 2003X
> 2003F
>
> Where F is for fall, S for spring, and X for summer session. Thus, a
> straight alphabetical sort doesn't give me what I want. If the year is the
> same, then I want a sort with S, then X, then F for identical year.
Consider storing the session in a separate field and assigning each
session a value that collates in the order you want.
Here's a way to order the sessions using their current format:
SELECT *
FROM records
ORDER BY lastName,
firstName,
SUBSTRING(term FROM 1 FOR 4),
CASE SUBSTRING(term FROM 5)
WHEN 'S' THEN 1
WHEN 'X' THEN 2
ELSE 3
END;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: An order by question
@ 2004-02-01 17:22 Tomasz Myrta <jasiek@klaster.net>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Tomasz Myrta @ 2004-02-01 17:22 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: darnold@northcoast.com; pgsql-sql
Dnia 2004-02-01 14:34, Użytkownik Michael Fuhr napisał:
> Consider storing the session in a separate field and assigning each
> session a value that collates in the order you want.
>
> Here's a way to order the sessions using their current format:
>
> SELECT *
> FROM records
> ORDER BY lastName,
> firstName,
> SUBSTRING(term FROM 1 FOR 4),
> CASE SUBSTRING(term FROM 5)
> WHEN 'S' THEN 1
> WHEN 'X' THEN 2
> ELSE 3
> END;
or the easier one:
order by lastname,firstname, translate(term,'SXF','123');
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: An order by question
@ 2004-02-01 19:11 Michael Fuhr <mike@fuhr.org>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2004-02-01 19:11 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: darnold@northcoast.com; pgsql-sql
On Sun, Feb 01, 2004 at 06:22:30PM +0100, Tomasz Myrta wrote:
> Dnia 2004-02-01 14:34, U¿ytkownik Michael Fuhr napisa³:
> > SELECT *
> > FROM records
> > ORDER BY lastName,
> > firstName,
> > SUBSTRING(term FROM 1 FOR 4),
> > CASE SUBSTRING(term FROM 5)
> > WHEN 'S' THEN 1
> > WHEN 'X' THEN 2
> > ELSE 3
> > END;
>
> or the easier one:
>
> order by lastname,firstname, translate(term,'SXF','123');
Blast...I just *knew* I was missing an easy, obvious solution.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-01 19:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-24 06:24 An order by question David Arnold <darnold@northcoast.com>
2004-02-01 13:34 Re: An order by question Michael Fuhr <mike@fuhr.org>
2004-02-01 17:22 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-01 19:11 ` Michael Fuhr <mike@fuhr.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