From: Michael Fuhr <mike@fuhr.org>
To: darnold@northcoast.com
Cc: pgsql-sql@postgresql.org
Subject: Re: An order by question
Date: Sun, 1 Feb 2004 06:34:51 -0700
Message-ID: <20040201133451.GA13002@winnie.fuhr.org> (raw)
> 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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, darnold@northcoast.com
Subject: Re: An order by question
In-Reply-To: <20040201133451.GA13002@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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