pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Orderby two different columns
3+ messages / 3 participants
[nested] [flat]

* Orderby two different columns
@ 2000-06-23 14:29 Mitch Vincent <mitch@venux.net>
  2000-06-23 15:59 ` Re: Orderby two different columns Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-23 16:07 ` Re: Orderby two different columns Oliver Elphick <olly@lfix.co.uk>
  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 14:29 Orderby two different columns Mitch Vincent <mitch@venux.net>
@ 2000-06-23 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
  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 14:29 Orderby two different columns Mitch Vincent <mitch@venux.net>
@ 2000-06-23 16:07 ` Oliver Elphick <olly@lfix.co.uk>
  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