pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrderby two different columns
3+ messages / 3 participants
[nested] [flat]
* Orderby two different columns
@ 2000-06-23 14:29 Mitch Vincent <mitch@venux.net>
0 siblings, 2 replies; 3+ messages in thread
From: Mitch Vincent @ 2000-06-23 14:29 UTC (permalink / raw)
To: pgsql-sql
I ran into a problem today that I hope someone can help me with...
I have a database (and application) that is used to track 'applicants'..
These applicants have two timestamp fields associated with their records and
both have relevance as to how long the applicant has been available..
The resubmitted field s of type timestamp and has a default value of 'Sat
Oct 02 00:00:00 1999 EDT'
I need to order search results by the two dates. Here is the problem..
They want whichever date is the most recent to appear on top.. If I do
'order by resubmitted desc,created desc' I get something like this :
Applicant Re-submitted Created
A 06/05/2000 12/31/1999
B 06/05/2000 12/31/1999
C 05/17/2000 02/09/2000
D 05/17/2000 01/21/2000
E 05/11/2000 01/27/2000
F 05/11/2000 01/21/2000
G 05/01/2000 12/31/1999
H 04/28/2000 01/28/2000
I 04/28/2000 01/12/2000
J 05/23//2000
Ok, see applicant J? I need him to be above C.. Basically what I need to do
is order by a combination of date created/resubmitted -- the way I'm doing
it now is going to list al the resubmitted's in order, then all the
created's in order.. Perhaps I'm just missing something simple, I sure hope
so..
Hopefully I've explained it well enough. Thanks for any suggestions!!!
-Mitch
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Orderby two different columns
@ 2000-06-23 15:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mitch Vincent <mitch@venux.net>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2000-06-23 15:59 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
"Mitch Vincent" <mitch@venux.net> writes:
> Ok, see applicant J? I need him to be above C.
Your example was about as transparent as mud, but maybe you are looking
to sort on MAX(resubmitted, created) or something like that? Anyway
I'd think that sorting on some function of the two dates is probably
what you need to do.
I don't think we have a two-input MAX function like that, but it'd be
easy enough to fake it with a CASE expression. Something like
... ORDER BY (CASE WHEN a > b THEN a ELSE b END) DESC;
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Orderby two different columns
@ 2000-06-23 16:07 Oliver Elphick <olly@lfix.co.uk>
parent: Mitch Vincent <mitch@venux.net>
1 sibling, 0 replies; 3+ messages in thread
From: Oliver Elphick @ 2000-06-23 16:07 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql; olly@linda.lfix.co.uk
"Mitch Vincent" wrote:
>I need to order search results by the two dates. Here is the problem..
>
>They want whichever date is the most recent to appear on top.. If I do
>'order by resubmitted desc,created desc' I get something like this :
>
>Applicant Re-submitted Created
>A 06/05/2000 12/31/1999
>B 06/05/2000 12/31/1999
>C 05/17/2000 02/09/2000
>D 05/17/2000 01/21/2000
>E 05/11/2000 01/27/2000
>F 05/11/2000 01/21/2000
>G 05/01/2000 12/31/1999
>H 04/28/2000 01/28/2000
>I 04/28/2000 01/12/2000
>J 05/23//2000
>
>
>Ok, see applicant J? I need him to be above C..
select * from table
order by case
when resubmitted > created then resubmitted
else created
end
desc;
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Train up a child in the way he should go, and when he
is old, he will not depart from it." Proverbs 22:6
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-06-23 16:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-23 14:29 Orderby two different columns Mitch Vincent <mitch@venux.net>
2000-06-23 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-23 16:07 ` Oliver Elphick <olly@lfix.co.uk>
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