1

I am trying to solve an interesting problem. I have a table that has, among other data, these columns (dates in this sample are shown in European format - dd/mm/yyyy):

n_place_id   dt_visit_date
(integer)    (date)
==========   =============
   1           10/02/2012
   3           11/03/2012
   4           11/05/2012
  13           14/06/2012
   3           04/10/2012
   3           03/11/2012
   5           05/09/2012
  13           18/08/2012

Basically, each place may be visited multiple times - and the dates may be in the past (completed visits) or in the future (planned visits). For the sake of simplicity, today's visits are part of planned future visits.

Now, I need to run a select on this table, which would pull unique place IDs from this table (without date) sorted in the following order:

  1. Future visits go before past visits
  2. Future visits take precedence in sorting over past visits for the same place
  3. For future visits, the earliest date must take precedence in sorting for the same place
  4. For past visits, the latest date must take precedence in sorting for the same place.

For example, for the sample data shown above, the result I need is:

 5     (earliest future visit)
 3     (next future visit into the future)
13     (latest past visit)
 4     (previous past visit)
 1     (earlier visit in the past)

Now, I can achieve the desired sorting using case when in the order by clause like so:

select
    n_place_id
from
    place_visit
order by
    (case when dt_visit_date >= now()::date then 1 else 2 end),
    (case when dt_visit_date >= now():: date then 1 else -1 end) * extract(epoch from dt_visit_date)

This sort of does what I need, but it does contain repeated IDs, whereas I need unique place IDs. If I try to add distinct to the select statement, postgres complains that I must have the order by in the select clause - but then the unique won't be sensible any more, as I have dates in there.

Somehow I feel that there should be a way to get the result I need in one select statement, but I can't get my head around how to do it.

If this can't be done, then, of course, I'll have to do the whole thing in the code, but I'd prefer to have this in one SQL statement.

P.S. I am not worried about the performance, because the dataset I will be sorting is not large. After the where clause will be applied, it will rarely contain more than about 10 records.

Aleks G
  • 56,435
  • 29
  • 168
  • 265

2 Answers2

2

With DISTINCT ON you can easily show additional columns of the row with the resulting n_place_id:

SELECT n_place_id, dt_visit_date
FROM  (
   SELECT DISTINCT ON (n_place_id) *
         ,dt_visit_date < now()::date    AS prio  -- future first
         ,@(now()::date - dt_visit_date) AS diff  -- closest first
   FROM   place_visit
   ORDER  BY n_place_id, prio, diff
   ) x
ORDER  BY prio, diff;

Effectively I pick the row with the earliest future date (including "today") per n_place_id - or latest date in the past, failing that.
Then the resulting unique rows are sorted by the same criteria.

Result:

 n_place_id | dt_visit_date
------------+--------------
 5          | 2012-09-05
 3          | 2012-10-04
 13         | 2012-08-18
 4          | 2012-05-11
 1          | 2012-02-10
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Try this

 select n_place_id
 from 
 (
    select *, 
    extract(epoch from (dt_visit_date - now())) as seconds,  
    1 - SIGN(extract(epoch from (dt_visit_date - now())) ) as futurepast
    from #t
 ) v
 group by n_place_id
 order by max(futurepast) desc, min(abs(seconds))
Aleks G
  • 56,435
  • 29
  • 168
  • 265
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • I get error `cannot cast interval to integer` in line `cast((dt_visit_date - now()) as int) as days` – Aleks G Sep 03 '12 at 09:52
  • Yep! This is about it. Just had to change `SIGN(...)` to `1 - SIGN(...)` - and it did the trick. Many thanks! – Aleks G Sep 03 '12 at 09:58
  • @AleksG: That's odd. I get correct results with podiluska's *original* query, not with your amended version. – Erwin Brandstetter Sep 04 '12 at 13:20
  • @ErwinBrandstetter That **is** odd. I had to put `1-` in to get the results I needed. – Aleks G Sep 04 '12 at 13:21
  • @AleksG: With correct results I mean the result displayed in my answer for the example in your question. podiluska edited his answer, maybe you built on his original version, but mistakenly edited his final version? – Erwin Brandstetter Sep 04 '12 at 13:29