Postgres Pro
Downloads
Home   >   mailing lists

Thread: An order by question

An order by question

From
David Arnold
Date:
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?

Re: An order by question

From
Michael Fuhr
Date:
> 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/

Re: An order by question

From
Tomasz Myrta
Date:
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

Re: An order by question

From
Michael Fuhr
Date:
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/